View Full Version : Solved: Excel 2003 - VBA Userform help
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
02-27-2009, 02:21 AM
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)
Thanks very much for coming back so quickly Simon. I'm afraid the code is still not working.
Simon Lloyd
02-27-2009, 03:17 AM
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?
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
02-27-2009, 06:29 AM
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
Simon - Many thanks for that, I'm pleased you told me where I was going wrong.
Simon Lloyd
02-27-2009, 07:06 AM
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"
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.