Populating a combobox from another combobox

[Pookiemeister]

As of now I have two tabs labeled hats and shoes. The first combo box displays in this case Hats and Shoes.

Private Sub cmbSDPFLine_Enter()    Dim i As Long
    cmbSDPFLine.Clear
    cmbPrdCde.Enabled = False
    cmbPrdCde.Clear
    txtbxPrdctNm.Text = ""
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    For i = 5 To WS_Count
        cmbSDPFLine.AddItem Sheets(i).Name
    Next
    
End Sub

If the user selects hats from the first combo box, on the spreadsheet with the tab labeled "Hats", starting in cell "A3" starts the list of different types of hats. As the user adds more hats to column A it should be added to the combo box. I thought the below code would work but it displays a "Run-time error '381': Could not set the List property. Invalid property array index."

Private Sub cmbPrdCde_Enter()    
    If cmbSDPFLine.Value = "Hats" Then
        cmbPrdCde.Clear
        cmbPrdCde.Value = ""
        With ThisWorkbook.Worksheets("Hats")
            Me.cmbPrdCde.List = .Range("A3").End(xlDown)


        End With
End SUB

Thank you to all for your help

 

[Fluff]

How about

   Me.cmbPrdCde.List = Application.Transpose(.Range("A3", .Range("A" & Rows.Count).End(xlUp)))

 

[Fluff]

Also rather than checking for the value of the combobox you can do

Private Sub cmbPrdCde_Enter()
        cmbPrdCde.Clear
        cmbPrdCde.Value = ""
        With ThisWorkbook.Worksheets(cmbSDPFLine.Value)
            Me.cmbPrdCde.List = Application.Transpose(.Range("A3", .Range("A" & Rows.Count).End(xlUp)))
        End With
End Sub

 

[Pookiemeister]

Thank you that worked great but what does "Transpose" do exactly?

 

[Fluff]

.Range("A3", .Range("A" & Rows.Count).End(xlUp))

will give a 2d array, but the combobox needs a 1d list, The Transpose turns the 2d array into a 1d array

 

[Pookiemeister]

How would I go about concatenating columns A and column B with this code. Is it possible? Column A will contain the item number for the hats and Column B will contain the name of the hat. Thank You.

 

[Fluff]

How about

Private Sub cmbPrdCde_Enter()
Dim ary As Variant, nary As Variant, r As Long
   cmbPrdCde.Clear
   cmbPrdCde.Value = ""
   With ThisWorkbook.Worksheets(cmbSDPFLine.Value)
      ary = .Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      ReDim nary(1 To UBound(ary))
         For r = 1 To UBound(ary)
            nary(r) = ary(r, 1) & ", " & ary(r, 2)
         Next r
   Me.cmbPrdCde.List = nary
   End With
End Sub

 

[Pookiemeister]

That worked wonderfully. Thank you. One question, is it possible to display just the Hat description into a read only textbox. For example if the user selects item 324-6 Tophat-Black. How would I just display "Tophat-Black" in a textbox. Thank you again.

 

[Fluff]

You have already started a new thread for this.
Please do not post the same question multiple times, as per Rule#12