VBA Combobox dependent on another Combobox...

[Charmwah]

Hi all

I have a simple userform (CustomerByProduct), where the user must make a selection from the 1st Combobox (cmdProductFamily, with the idea being that this choice then determines the options shown in the 2nd Combobox (cmdProductName). The choice from the 1st relates to a named range of the same name. However, I can't get the 2nd Combobox list to populate with the code i'm using, and I can't work out where i'm going wrong...

Option Explicit
Private Sub cmdProductFamily_Change()
    If Me.cmdProductFamily.Value = "AA" Then Me.cmdProductName.RowSource = "AA"
    If Me.cmdProductFamily.Value = "BB" Then Me.cmdProductName.RowSource = "BB"
End Sub
Sub ShowDialogBox()
    CustomerByProduct.Show
End Sub

I also tried it the other way around, and that didn't help...

Option Explicit
Private Sub cmdProductName_Change()
    If Me.cmdProductFamily.Value = "AA" Then Me.cmdProductName.RowSource = "AA"
    If Me.cmdProductFamily.Value = "BB" Then Me.cmdProductName.RowSource = "BB"
End Sub
Sub ShowDialogBox()
    CustomerByProduct.Show
End Sub

The RowSource property box for the 2nd Combobox is blank.

I'm a relative noob, so its sure to be a simple error, but any help greatly appreciated!

 

[Fluff]

Try

Private Sub cmdProductFamily_Change()
   If Me.cmdProductFamily.Value = "AA" Then Me.cmdProductName.RowSource = Range("AA").Address
   If Me.cmdProductFamily.Value = "BB" Then Me.cmdProductName.RowSource = Range("BB").Address
End Sub

 

[Charmwah]

Hi Fluff

That didn't work either i'm afraid. I've attached the link here in case it helps?

Try

Private Sub cmdProductFamily_Change()
   If Me.cmdProductFamily.Value = "AA" Then Me.cmdProductName.RowSource = Range("AA").Address
   If Me.cmdProductFamily.Value = "BB" Then Me.cmdProductName.RowSource = Range("BB").Address
End Sub

 

[Fluff]

That code needs to go in the userform module, not a regular module.

 

[Charmwah]

Thats excellent, thanks Fluff. I knew it would be a noob mistake.

That code needs to go in the userform module, not a regular module.

 

[Fluff]

Glad to help & thanks for the feedback

 

[adambc]

@Fluff

Help please!!!

Have adapted your code above to ...

Private Sub cboStrategyAlignment_Change()
   If cboStrategyAlignment.value = "Engagement" Then cboSpokeFocus.RowSource = Sheets("Look Up Tables").Range("Engagement").Address
   If cboStrategyAlignment.value = "Efficiency" Then cboSpokeFocus.RowSource = Sheets("Look Up Tables").Range("Efficiency").Address
   If cboStrategyAlignment.value = "Improvement" Then cboSpokeFocus.RowSource = Sheets("Look Up Tables").Range("Improvement").Address
End Sub

... which works perfectly if the active worksheet is Look Up Tables ...

But I need the code to work when the active worksheet is another worksheet in the same workbook - I thought adding Sheets("Look Up Tables"). infront of Range would do it, but it doesn't!

What am I missing?

Thanks ...

 

[adambc]

@Fluff

PS; have also tried Worksheets("Look Up Tables"). but that doesn't work either?!!!

 

[adambc]

@Fluff

Got it ...

Private Sub cboStrategyAlignment_Change()

With Sheets("Look Up Tables")
   If cboStrategyAlignment.value = "Engagement" Then cboSpokeFocus.List = .Range("Engagement").value
   If cboStrategyAlignment.value = "Efficiency" Then cboSpokeFocus.List = .Range("Efficiency").value
   If cboStrategyAlignment.value = "Improvement" Then cboSpokeFocus.List = .Range("Improvement").value
End With

End Sub

... unless there's a better way?

Thanks ...

 

[Fluff]

Doing like that is fine