Combobox Problem Re-Visited

[Dkso]

Hi, and Happy New Year to one and All.

I am having and have had a problem with putting a Combobox on a form in
Excel. I have the following code but when executed nothing is listed in the
combobox for selection.

myDesc = WorksheetFunction.VLookup(ComboBox1.Value, _
Worksheets("Sheet1").Range("A1:B16"), 2, False)
Label4.Caption = myDesc

This procedure has been put into, the Change, the Enter the Enter and at one
time I put it into the BeforeUpdate event.

I have "Sheet1" set-up with a list of 16 "item codes" in column "A" and 16
"Descriptions" in Column "B".
Label4 is to show the corresponding item Description, when the item code is
selected.


Help please!

Thanks
Dean

 

[Jason Morin]

I used this in a standard module:

Sub ShowIt()
Load UserForm1
UserForm1.ComboBox1.RowSource = "A1:A16"
UserForm1.Show
End Sub

and then used this change event for the combobox:

Sub ComboBox1_Change()
Dim myDesc As Variant
Dim comboVal As Long
Dim lookupTbl As Range
Set lookupTbl = Worksheets("Sheet1").Range("A1:B16")
comboVal = UserForm1.ComboBox1.Value
myDesc = Application.WorksheetFunction. _
VLookup(comboVal, lookupTbl, 2, False)
Label4.Caption = myDesc
End Sub

----
Just a note - in my test, the item codes are numerical
values, so I set the comboVal to Long data type.

HTH
Jason
Atlanta, GA

 

[Tom Ogilvy]

It should either raise an error because the value of Combbox1 was not found
at the time of execution or it should return the value of myDesc to the
label. If neither of those are happening, then it is probably not being
executed. An additional possibility is that it is excuting and raising an
error but you have suppressed the error. This would give the impression
that nothing is happening. Remove any error suppression while you are
debugging.

 

[Dkso]

Tom, Jason,

Thanks both for your help. I was still having problems even after I started
using Jason's code. I was just stripping the spreadsheet to upload it and
it worked. Turns out it must be because of the other sheets I already had
in the workbook.

Thanks again

Happy New Year to one and all.

Dean