Excel/VBA ComboBox


Hi Jan,

I am new to VBA, so I need some help figuring out how to do a couple of things. At first, I created an ActiveX combo box right inside my worksheet. In this combo box I have a range of Part Numbers. I wanted to make it so that once someone chooses a part number from that combo box list, in another cell a VLOOKUP formula would look up the corresponding Category to that part number. For example, Someone chooses the part # 4-635917-7 from the combobox list and in cell B2 the category ' Enclosures' shows up-- the formula i have in B2 is
=IF(LinkedCell="","",IFERROR(VLOOKUP(LinkedCell,VlookupTable,2,FALSE),IFERROR(VLOOKUP(1*LinkedCell,VlookupTable,2,FALSE),"Not Found")))

'LinkedCell' Is the cell that is linked to my combobox.
This formula works perfect and it does exactly what i want it to do, however,  I want to do is make this a lot cleaner. I want it so when someone goes into this worksheet a userform pops up and asks them to select the part number they have (from the combobox) and right beside it it will tell them which category corresponds to that part number. Right now I have created the userform and the combobox. The code I have used so far is,

Private Sub UserForm_Initialize()

Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Part#&CategVLOOKUP")

For Each cLoc In ws.Range("Part_Number")
With Me.ComboBox1
   .AddItem cLoc.Value
End With
Next cLoc

End Sub

And that seems to create the combo box for me... Now what i need to do is make it so that a list or something shows up beside it and tells me which product category corresponds to that part number they have chosen. I know how to do that in formulas... but no idea how to do it in VBA... Can you tell me what I should do next and what the coding should be in order for me to do this??

I've attached an image to give you a better idea!

Also, if it makes things easier for explaining I have a seperate sheet called 'VLOOKUP' and this is where my vlookup table is. In the first column I have a range named "Part_Number", the second column is the Category that corresponds to that part number.

I appreciate your help so much!

Thank you Jan

You can add both the partnumber and the category column to the listbox like so:

With Me.ComboBox1
For Each cLoc In ws.Range("Part_Number")
   .AddItem cLoc.Value
   .List(.ListCount - 1,1) = cLoc.Offset(,1).Value
Next cLoc
End With

Or -shorter and faster- (if they are located on adjacent columns):

Me.ComboBox1.List = ws.Range("Part_Number").Resize(,2).Value

NB: I suggexst you to change the control name to something meaningful.

In the ComboBox1_Change event you can then update a label with the cat:

lblCategory.Caption = ComboBox1.List(ComboBox1.ListIndex,1)

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2017 About.com. All rights reserved.