(Solved) – Find Data in Sheet and fill textboxes in User Form

(solved)-–-find-data-in-sheet-and-fill-textboxes-in-user-form

I stuck in following Prob.
What do I have ?
I Have a UserForm (UF) and 9 Textboxes
1Prename
2Name
3KST
4Code
5PhoneNr
6MailAdress
etc
9Ware

I Have in the same Workbook a Sheet named “DATA”
Column E named Ware
Column J named PreName
Column K named Name
Column L until P the rest of the mentioned Values
The Data Sheet is not static. Means It grows by record a new entry (new Customer)

What should the code do?
What will I do?
In a first Step
By Entering the Prename and Name in the Textboxes it should check the Sheet “DATA” if the Pre,-Nname and the rest of the Information belonging to the Prename , Name already exist.
If it exists, if there is a match.. a Msgbx should appear with all found Prenames, Names, etc
For Exmpl Peter Mayer; Peter Maier; Peter Meier … Code, Mailadress, MailCC, Phone, KST
Msgbx “Prename (Peter), Name (Mayer,Maier,Meier); Code, MailAdress, MaiCC, Phone, KST”; vbCHoose a Customer and submit all Informations to UserForm; Vb Not Found New Entry in User Form means.. back to User Form

In a 2nd Step
If there was a match and the Textboxes are filled with the found Adress and other Data from the sheet UF.Ware.Value should be checked if the Costumer ordered a new Item or the Same Item again.
Means:by typing in the Item in UF.Ware.Value check Sheets(“Data”) Column E.
If its matching Submit to User Form if it does not matcvh then also submit to Textbox Ware in User Form.

What have I done?
I wanted to create Step 1 but as a newbe on VBA I stuck
Heres my Code

Dim Vorname As String
Dim FName As String
Dim OrgCode As String
Dim TeleNr As Byte
Dim MailArdess As Integer
Dim cNum As Integer


cNum = 9


Set Reg1 = UserForm1.Vorname
Set Reg2 = UserForm1.FName
Set Reg3 = UserForm1.KST
Set Reg4 = UserForm1.OrgaCode
Set Reg5 = UserForm1.TeleNr
Set Reg6 = UserForm1.MailAdress

'Check to see if value exists
If WorksheetFunction.CountIf(Sheets("DATA").Range("J:P"), Me.Vorname.Value) = 0 Then
MsgBox "Name nicht korrekt"
Me.Vorname.Value = ""
Exit Sub
End If

With Me
Vorname = Application.WorksheetFunction.VLookup(UserForm1.Vorname.Value(Me.Vorname), Sheets("DATA").Range("Lookup"), 11, 0)
'.Reg3 = Application.WorksheetFunction.VLookup(KST(Me.Vorname), Sheets("DATA").Range("Lookup"), 16, 0)
'.Reg4 = Application.WorksheetFunction.VLookup(OrgaCode(Me.Vorname), Sheets("DATA").Range("Lookup"), 12, 0)
'.Reg5 = Application.WorksheetFunction.VLookup(TeleNr(Me.Vorname), Sheets("DATA").Range("Lookup"), 15, 0)
'.Reg6 = Application.WorksheetFunction.VLookup(MailAdress(Me.Vorname), Sheets("DATA").Range("Lookup"), 16, 0)
End With
End Sub

Hoping you have abetter idea
Thx Peter

Leave a Reply

Your email address will not be published. Required fields are marked *