Vlookup value in textbox to determine combobox value

[MrManBoy]

Hi Team,

What I am trying to achieve:

On my user-form, there is a textbox user's can enter a value. Following the textbox is a combo box. I am looking for a way that the combo box can vlookup the value in the textbox to determine it's (the combo box) own value.

I can do this by creating multiple list and using the row-source field in the combo box, but the problem with that I will be looking at 100's of lists which I don't mind compiling but thought there may be a more efficient way to do this.

Any help will be greatly appreciated

If the above didn't make sense, my apologies and please advise so I amy try explaining it again

 

[Worf]

Hi mate

Here are the main features of the example below:

- It uses a 4-column combo box named cmbOutlet
- The combo box is populated from a1:d10
- The text box is labeled tbCost and it relates to data at column D
- Click the grey area of the form to see the combo box change, based on the text box value.

If this is not what you want, please explain.

Private Sub UserForm_Initialize()
Dim ws5 As Worksheet, range_a As Range
Set ws5 = Worksheets("Setup")
Me.cmbOutlet.ColumnCount = 4
For Each range_a In ws5.Range("a1:a10")
With Me.cmbOutlet
  .AddItem range_a.Value
  .List(.ListCount - 1, 1) = range_a.Offset(, 1)
  .List(.ListCount - 1, 2) = range_a.Offset(, 2)
  .List(.ListCount - 1, 3) = range_a.Offset(, 3)
End With
Next
End Sub

Private Sub UserForm_Click()
Me.cmbOutlet.ListIndex = Evaluate("=match(""" & tbCost.Text & """,d1:d10,0)") - 1
End Sub

 

[MrManBoy]

Thanks Worf - I will test and play with this and get back to you when practical until then (next day or 2), thanks for the help

 

[MrManBoy]

Hi Worf - Do you have a workbook with this example already implemented I could look at? If not, I will go ahead and build one according to your code.

Sorry, Ive only just had time to look at the code you have given.


Cheers

Hi mate

Here are the main features of the example below:

- It uses a 4-column combo box named cmbOutlet
- The combo box is populated from a1:d10
- The text box is labeled tbCost and it relates to data at column D
- Click the grey area of the form to see the combo box change, based on the text box value.

If this is not what you want, please explain.

Private Sub UserForm_Initialize()
Dim ws5 As Worksheet, range_a As Range
Set ws5 = Worksheets("Setup")
Me.cmbOutlet.ColumnCount = 4
For Each range_a In ws5.Range("a1:a10")
With Me.cmbOutlet
  .AddItem range_a.Value
  .List(.ListCount - 1, 1) = range_a.Offset(, 1)
  .List(.ListCount - 1, 2) = range_a.Offset(, 2)
  .List(.ListCount - 1, 3) = range_a.Offset(, 3)
End With
Next
End Sub

Private Sub UserForm_Click()
Me.cmbOutlet.ListIndex = Evaluate("=match(""" & tbCost.Text & """,d1:d10,0)") - 1
End Sub

 

[Worf]

I gave it a quick test and appears to be working:

https://dl.dropboxusercontent.com/u/52116836/uform.xlsm

 

[MrManBoy]

Hi Worf - Thanks for this, finally had a chance to look at what you explained, it is kind of what I am looking for.

I need the combo box value to work like a dependent data validation list based on the value of the textbox which is very similar to what you got. Here is link to a file, that I hope explains this a little better for you or anyone else reading this. My apologies.

https://goo.gl/FUtcl7

 

[Worf]

Hi
Please test this:

Dim lr%


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' column A has numbers, not text
' Enter or Tab to leave text box
Me.ComboBox1.ListIndex = Evaluate("=match(" & CInt(TextBox1.Text) & ",a2:a" & lr & ",0)") - 1
End Sub


Private Sub UserForm_Initialize()
Dim ws5 As Worksheet, range_a As Range
Set ws5 = Worksheets("PostCodes")
Me.ComboBox1.ColumnCount = 1
lr = ws5.Range("a" & Rows.Count).End(xlUp).Row
For Each range_a In ws5.Range("b2:b" & lr)
    Me.ComboBox1.AddItem (range_a.Value)
Next
End Sub

 

[MrManBoy]

Thanks Worf - this works great mate! However just asking if I could change the combo-box to another textbox? I tried changing it but kept getting a method/data error.

So when postcode is entered in txtbx1, the name of that suburb appears in textbox2...similar how you have done with the combo-box.

Sorry just trying to trap the data to allow very little user changes. - cheers mate

 

[Worf]

Try something like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' column A has numbers, not text
' Enter or Tab to leave text box
Dim r As Range
Set r = Sheets("PostCodes").Columns(1).Find(CDbl(Me.TextBox1.Text), _
LookIn:=xlValues, lookat:=xlWhole)
Select Case r Is Nothing
    Case True
        Me.TextBox2.Text = ""
    Case False
         Me.TextBox2.Text = r.Offset(, 1)
End Select
End Sub

 

[MrManBoy]

Bloody fantastic Worf! Really appreciate the time and help you have provided - just made my life easier! - Have a good one! ray: