Combobox Problem Re-Visited
 

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

Combobox Problem Re-Visited
 

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

-----Original Message-----
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


.

Combobox Problem Re-Visited
 

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.


--
Regards,
Tom Ogilvy

"Dkso" wrote in message
...
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

Combobox Problem Re-Visited
 

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
"Dkso" wrote in message
...
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