PDA

View Full Version : Solved: Vlookup help filling combo box



crender2000
06-21-2012, 11:57 AM
What I am trying to do is fill the combobox depending on what the user types in the textbox. For example it the user types dog the combobox will be filled with two. Then the user would write the entire userform entries to another sheet. I will include a sample workbook when I can figure out how to add attachment. This is just a simple sample. I need it to work if the sheet is updating and the sheet size for the vlookup changes. Like using .xldown for the range.

Ok here is the error message I get. I am fairly new to VBA I do have a little programming knowledge.
"Could not set the property value. Type mismatch

Finally, once someone figures this out can they show me an example of the lookup value in say D column and the value to be displayed say in the g column.


Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet2.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
MsgBox "One record written to Sheet3"
MsgBox "Do you want to enter another record?", vbYesNo
If vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

Private Sub TextBox1_Change()
ComboBox1.Value = Application.VLookup(Me.TextBox1, Sheets("Sheet1").Range("A:C"), 2, False)

CatDaddy
06-21-2012, 01:11 PM
try:
ComboBox1.AddItem Application.VLookup(TextBox1.Text, Sheets("Sheet1").Range("A:C"), 2, False)

and dont forget End Sub at the end of the textbox change event

crender2000
06-21-2012, 01:21 PM
The end sub was there just did not copy and paste far enough.
After thinking about the problem I realized I was coding in the text box change event when it should be in the text box exit event. Once I changed that my code worked.

CatDaddy
06-21-2012, 01:22 PM
great, glad its working for you

crender2000
06-21-2012, 01:26 PM
Thanks for your help.

Tinbendr
06-21-2012, 01:28 PM
Another approach.

crender2000
06-21-2012, 01:34 PM
Here is what I wanted

crender2000
06-21-2012, 01:44 PM
Ok now how in the world do you mark this thread solved. It should be simple but I can not figure it out for the life of me.

Aussiebear
06-21-2012, 02:20 PM
Go to the first post and just above that should be a Thread Tools dropdown. I have marked this as solved for you on this occasion.

crender2000
06-22-2012, 02:57 AM
There is no drop down on thread tools. I went to FAQ and found there was suppose to be one. I also used the vba tag and I can not tell it did any thing. I love the help here but the website is very confusing to navigate.

Aussiebear
06-22-2012, 04:19 AM
What browser are you using?