ComboBox String

[DAWG2006]

Good evening everyone,
What I am trying to accomplish is to have a ComboBox (we will call this ComboBox1) populate a list of data. When one of those data points are selected using ComboBox1 I need it to populate and additional list of data points (different info) in ComboBox2.

In other words, I need the ComboBox to act similar to the vlookup function however instead of a single point I need the ComboBox to produce a list. Below is the code I have started:

Private Sub UserForm_Initialize()
 Dim cPart As Range
 Dim cLoc As Range
 Dim ws As Worksheet
 Set ws = Worksheets("Catalog")
 
 For Each cPart In ws.Range("type")
  With Me.ComboBox1
      .AddItem cPart.Value
      .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
 Next cPart

End Sub

I have not added ComboBox2 in the code because of the unknown coding for what is required.

Thank you for the assistance in advance and if you need any additional information to fix my problem please ask.

DAWG2006

 

[Norie]

How is the data for the 2nd combobox arranged?

How can the data for it be recognised/found based on the selection in the 1st combobox?

 

[DAWG2006]

What I have is 15 worksheets that are broken down by 15 names. Each worksheet has Column A as product names, and Column B as product codes. I would like ComboBox1 to be selectable of one of the 15 worksheet names, ComboBox2 listing the names from the corresponding worksheet, and then a text box auto filling with the corresponding column B information (i.e.; Worksheet3 = Cell A3 = Cell B3) in the respective order.

I hope this helps

 

[DAWG2006]

Bump, please help

 

[Norie]

Sorry for not getting back to you - I must have missed the notification.

Anyway, are the 15 worksheets the only ones in the workbook or are they easily identifiable by something, eg name.

Or even better is their a list of their names.

We need one or the other to populate the first combobox.

Once that's done it should be straightforward to do the rest.

For example to populate the 2nd combobox:

Private Sub ComboBox1_Change()
Dim wsName As Range
Dim rngItems As Range
Dim LastRow As Long
 
   If ComboBox1.ListIndex<>-1 Then
 
      Set wsName = Worksheets(ComboBox1.Value)
 
      LastRow = wsName.Range("A"& Rows.Count).End(xlUp).Row
 
      ' change A1 to A2 if there is a header
      Set rngItems = Worksheets(ComboBox1.Value).Range("A1:A" & LastRow)
 
      ComboBox2.List = rngItems.Value
 
   End If
 
End Sub