linking combobox value with VLOOKUP to a textbox in userform

[NDMDRB]

Hello,

I have a sheet "Clients" that contains all clients details as the table below and I have all these data as dynamic named range "Client_Details" "A2:D4"

I have a userform with many text boxes and combo boxes, but what I need right now is:

• Once I choose a Client name in the combobox1, get the ID for this client on textbox1 and the phone number on textbox2
  • ex: if I typed "Nael" from the combobox1, get the ID "102" on textbox1 and the phone number "654654" on textbox2
• If I typed a name that not exist, get msgbox ("This Client Name is not Exist" & vbCrLf & vbCrLf & "Do you wanna add a new client?" , vbYesNo)
  • If vbYes then, open the userform "NewClient" (And add a new client and then update the list on combobox1 to get the new client)
  • If vbNo, combobox1 = ""

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]Nadim[/TD]
[TD]123456[/TD]
[TD]sadas[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]102[/TD]
[TD]Nael[/TD]
[TD]654654[/TD]
[TD]asdasd[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]103[/TD]
[TD]Rami[/TD]
[TD]545645[/TD]
[TD]asfdaf[/TD]
[/TR]
</tbody>[/TABLE]


Many thanks in advanced

 

[Fluff]

For the first part, how about

Dim UfDic As Object

Private Sub ComboBox1_AfterUpdate()
   With Me.ComboBox1
      If UfDic.exists(.Value) Then
         Me.TextBox1.Value = UfDic.Item(.Value)(0)
         Me.TextBox2.Value = UfDic.Item(.Value)(1)
      Else
         MsgBox .Value & " does not exist"
      End If
   End With
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   
   Set UfDic = CreateObject("scripting.dictionary")
   UfDic.CompareMode = 1
   For Each Cl In Range("Client_Details").Columns(2).Rows
      If Not UfDic.exists(Cl.Value) Then UfDic.Add Cl.Value, Array(Cl.Offset(, -1).Value, Cl.Offset(, 1).Value)
   Next Cl
   Me.ComboBox1.List = UfDic.keys
End Sub

 

[NDMDRB]

Thank you so much Fluff,

It works great, I just need one more help regarding the msgbox if yes or no, I added the below new lines to the provided code, but I got an error and don't know what it is!!

So, what I need is, once the msgbox pop up, If I pressed no, just close the msgbox and continue, if I pressed yes, show the form "frmNewClient"

Here is the updated code

Private Sub [COLOR=#333333]ComboBox1[/COLOR]_AfterUpdate()
Dim MsgBoxResult As Long
   With Me.[COLOR=#333333]ComboBox1[/COLOR]
      If UfDic.exists(.Value) Then
         Me.TextBox1.Value = UfDic.Item(.Value)(0)
         Me.Textbox2.Value = UfDic.Item(.Value)(1)
      Else
         MsgBoxResult = MsgBox(.Value & "!!!" & vbCrLf & vbCrLf & "The above client name is not Exist" & vbCrLf & vbCrLf & "Do you wanna add a new client?", vbYesNo)


If MsgBoxResult = vbYes Then
frmNewClient.Show
Else


End If


End Sub

 

[Fluff]

How about

Private Sub ComboBox1_AfterUpdate()
   With Me.ComboBox1
      If UfDic.exists(.Value) Then
         Me.TextBox1.Value = UfDic.Item(.Value)(0)
         Me.TextBox2.Value = UfDic.Item(.Value)(1)
      Else
         If MsgBox(.Value & "!!!" & vbCrLf & vbCrLf & "The above client name is not Exist" & vbCrLf & vbCrLf & "Do you wanna add a new client?", vbYesNo) = vbYes Then
            frmNewClient.Show
         End If
      End If
   End With
End Sub