Solved: Excel 2003 - VBA Userform help

[hmws]

I have created a userform with a combo box and three text boxes.

I would like the the three text boxes to display a result from the value of the combo box.

I have entered

Sub CmbOrdNum_Change()

Textbox2.text=Application.Worksheetfunction.Vlookup(CmbOrdNum.value,Workshe ets("Sheet1").Range("B8:BT350"),3)

End Sub

Can someone explain what I am doing wrong here?

Thanks very much

[Simon Lloyd]

I have created a userform with a combo box and three text boxes.

I would like the the three text boxes to display a result from the value of the combo box.

I have entered

Sub CmbOrdNum_Change()

Textbox2.text=Application.Worksheetfunction.Vlookup(CmbOrdNum.value,Workshe ets("Sheet1").Range("B8:BT350"),3)

End Sub

Can someone explain what I am doing wrong here?

Thanks very muchJust a couple of incomplete statements, it should look like this:

Me.TextBox2.Value = Application.WorksheetFunction.VLookup(Me.CmbOrdNum.Value, Worksheets("Sheet1").Range("B8:BT350"),3, False)

[hmws]

Thanks very much for coming back so quickly Simon. I'm afraid the code is still not working.

[Simon Lloyd]

It worked perfect for me!, why not post a workbook with the userform and ALL code so we can see whats happening?

Anyway, when you say its not working what do you mean?, can you explain?

[hmws]

It worked perfect for me!, why not post a workbook with the userform and ALL code so we can see whats happening?

Anyway, when you say its not working what do you mean?, can you explain?

Hi Simon

Thanks for coming back. I have created a small worksheet with code (attached). The same thing is happening with this so its clearly something I am failing to understand.

Once again thanks for coming back.

Steve

[Simon Lloyd]

Its because you are looking up a number, the vlookup is looking for a string via the combobox, it will work if you declare the function of the combobox like this:Sub ComboBox1_Change()
Dim MyVal As Integer
MyVal = Me.ComboBox1.Value
Me.TextBox1.Value = Application.WorksheetFunction.VLookup(MyVal, Worksheets("Sheet1").Range("A2:F5"), 3, False)
End Sub

[hmws]

Simon - Many thanks for that, I'm pleased you told me where I was going wrong.

[Simon Lloyd]

hmws,
If your query has been solved please take a moment to let us and eveyone else know by going to, Thread Tools > "Mark This Thread As Solved"