antonhoward
New Member
- Joined
- Jan 24, 2008
- Messages
- 9
Hello Folks,
I need some assistance with a macro i'm using with linked combo boxes.
I have three combo boxes, the first combobox dictates the choices shown in the second combobox and so on.
The trouble is that I need to make the coding that I have already, follow on to another 45 combo boxes and do not want to have to create the coding that many times over.
Basically I need to know if
A> you can list more than one combo box in the line of code and how
or
B> use some sort of sub or wildcard that can be used instead of the combo box numbers.
Coding is as follows:
Private Sub userform_Activate()
'Set AddItem for Combobox1
ComboBox5.AddItem "Bar"
ComboBox5.AddItem "Bowling"
ComboBox5.AddItem "Food"
End Sub
Private Sub ComboBox5_Change()
'It's an Empty Box'
If ComboBox5 = "" Then
ComboBox6.Clear
ComboBox6.AddItem ""
End If
'It's a Bar Item'
If ComboBox5 = "Bar" Then
ComboBox6.Clear
ComboBox6.AddItem "Cider"
ComboBox6.AddItem "Draught"
ComboBox6.AddItem "Minerals"
ComboBox6.AddItem "Multi Buys"
ComboBox6.AddItem "PPL"
ComboBox6.AddItem "PPS"
ComboBox6.AddItem "Spirits"
ComboBox6.AddItem "Sundries"
ComboBox6.AddItem "Wine"
End If
'It's a Bowling Item'
If ComboBox5 = "Bowling" Then
ComboBox6.Clear
ComboBox6.AddItem "Adult Units"
ComboBox6.AddItem "Birthday Party"
ComboBox6.AddItem "Family Units"
ComboBox6.AddItem "Junior Units"
ComboBox6.AddItem "Other"
ComboBox6.AddItem "Other Adult Units"
ComboBox6.AddItem "Other Packages"
ComboBox6.AddItem "Promotions"
End If
'It's a Food Item'
If ComboBox5 = "Food" Then
ComboBox6.Clear
ComboBox6.AddItem "Birthday"
ComboBox6.AddItem "Drinks"
ComboBox6.AddItem "Other"
ComboBox6.AddItem "Party Packages"
ComboBox6.AddItem "Take 10"
End If
End Sub
Private Sub ComboBox6_Change()
'It's an Empty Box'
If ComboBox6 = "" Then
ComboBox7.Clear
ComboBox7.AddItem ""
End If
'It's Cider'
If ComboBox6 = "Cider" Then
ComboBox7.Clear
ComboBox7.AddItem "Bulmers"
ComboBox7.AddItem "Jacques"
ComboBox7.AddItem "Magners"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Strongbow"
ComboBox7.AddItem "Strongbow Jug"
End If
'It's a Draught'
If ComboBox6 = "Draught" Then
ComboBox7.Clear
ComboBox7.AddItem "Fosters"
ComboBox7.AddItem "Fosters Jug"
ComboBox7.AddItem "Guiness"
ComboBox7.AddItem "Guiness Jug"
ComboBox7.AddItem "John Smiths"
ComboBox7.AddItem "John Smiths Jug"
ComboBox7.AddItem "Kronenbourg"
ComboBox7.AddItem "Kronenbourg Jug"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Stella"
ComboBox7.AddItem "Stella Jug"
End If
'It's a Mineral'
If ComboBox6 = "Minerals" Then
ComboBox7.Clear
ComboBox7.AddItem "Baby Juice"
ComboBox7.AddItem "Cordial"
ComboBox7.AddItem "Fruit Shoot"
ComboBox7.AddItem "J2O"
ComboBox7.AddItem "NRB"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "PostMix"
ComboBox7.AddItem "Red Bull"
ComboBox7.AddItem "Slush"
ComboBox7.AddItem "Water"
End If
'It's a Multi Buy'
If ComboBox6 = "Multi Buys" Then
ComboBox7.Clear
ComboBox7.AddItem "Becks"
ComboBox7.AddItem "Budweiser"
ComboBox7.AddItem "Corkys"
ComboBox7.AddItem "Fosters"
ComboBox7.AddItem "J20"
ComboBox7.AddItem "Kronenbourg"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Strongbow"
ComboBox7.AddItem "VK"
End If
'It's a PPL'
If ComboBox6 = "PPL" Then
ComboBox7.Clear
ComboBox7.AddItem "Becks"
ComboBox7.AddItem "Budweiser"
ComboBox7.AddItem "Corona"
ComboBox7.AddItem "Fosters"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Stella"
End If
'It's a PPS'
If ComboBox6 = "PPS" Then
ComboBox7.Clear
ComboBox7.AddItem "Smirnoff Ice"
ComboBox7.AddItem "VK"
End If
'It's Spirits'
If ComboBox6 = "Spirits" Then
ComboBox7.Clear
ComboBox7.AddItem "Brandy"
ComboBox7.AddItem "Corkys"
ComboBox7.AddItem "Gin"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Rum"
ComboBox7.AddItem "Schnapps"
ComboBox7.AddItem "Vodka"
ComboBox7.AddItem "Whiskey"
End If
'It's Sundries'
If ComboBox6 = "Sundries" Then
ComboBox7.Clear
ComboBox7.AddItem "Crisps"
ComboBox7.AddItem "Nuts"
ComboBox7.AddItem "Snacks"
End If
'It's Wine'
If ComboBox6 = "Wine" Then
ComboBox7.Clear
ComboBox7.AddItem "Champagne"
ComboBox7.AddItem "Party"
ComboBox7.AddItem "Red"
ComboBox7.AddItem "Red SSB"
ComboBox7.AddItem "Rose"
ComboBox7.AddItem "White"
ComboBox7.AddItem "White SSB"
End If
End Sub
Hope you can help as you can probably tell I'm not fluent in this stuff.


I need some assistance with a macro i'm using with linked combo boxes.
I have three combo boxes, the first combobox dictates the choices shown in the second combobox and so on.
The trouble is that I need to make the coding that I have already, follow on to another 45 combo boxes and do not want to have to create the coding that many times over.
Basically I need to know if
A> you can list more than one combo box in the line of code and how
or
B> use some sort of sub or wildcard that can be used instead of the combo box numbers.
Coding is as follows:
Private Sub userform_Activate()
'Set AddItem for Combobox1
ComboBox5.AddItem "Bar"
ComboBox5.AddItem "Bowling"
ComboBox5.AddItem "Food"
End Sub
Private Sub ComboBox5_Change()
'It's an Empty Box'
If ComboBox5 = "" Then
ComboBox6.Clear
ComboBox6.AddItem ""
End If
'It's a Bar Item'
If ComboBox5 = "Bar" Then
ComboBox6.Clear
ComboBox6.AddItem "Cider"
ComboBox6.AddItem "Draught"
ComboBox6.AddItem "Minerals"
ComboBox6.AddItem "Multi Buys"
ComboBox6.AddItem "PPL"
ComboBox6.AddItem "PPS"
ComboBox6.AddItem "Spirits"
ComboBox6.AddItem "Sundries"
ComboBox6.AddItem "Wine"
End If
'It's a Bowling Item'
If ComboBox5 = "Bowling" Then
ComboBox6.Clear
ComboBox6.AddItem "Adult Units"
ComboBox6.AddItem "Birthday Party"
ComboBox6.AddItem "Family Units"
ComboBox6.AddItem "Junior Units"
ComboBox6.AddItem "Other"
ComboBox6.AddItem "Other Adult Units"
ComboBox6.AddItem "Other Packages"
ComboBox6.AddItem "Promotions"
End If
'It's a Food Item'
If ComboBox5 = "Food" Then
ComboBox6.Clear
ComboBox6.AddItem "Birthday"
ComboBox6.AddItem "Drinks"
ComboBox6.AddItem "Other"
ComboBox6.AddItem "Party Packages"
ComboBox6.AddItem "Take 10"
End If
End Sub
Private Sub ComboBox6_Change()
'It's an Empty Box'
If ComboBox6 = "" Then
ComboBox7.Clear
ComboBox7.AddItem ""
End If
'It's Cider'
If ComboBox6 = "Cider" Then
ComboBox7.Clear
ComboBox7.AddItem "Bulmers"
ComboBox7.AddItem "Jacques"
ComboBox7.AddItem "Magners"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Strongbow"
ComboBox7.AddItem "Strongbow Jug"
End If
'It's a Draught'
If ComboBox6 = "Draught" Then
ComboBox7.Clear
ComboBox7.AddItem "Fosters"
ComboBox7.AddItem "Fosters Jug"
ComboBox7.AddItem "Guiness"
ComboBox7.AddItem "Guiness Jug"
ComboBox7.AddItem "John Smiths"
ComboBox7.AddItem "John Smiths Jug"
ComboBox7.AddItem "Kronenbourg"
ComboBox7.AddItem "Kronenbourg Jug"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Stella"
ComboBox7.AddItem "Stella Jug"
End If
'It's a Mineral'
If ComboBox6 = "Minerals" Then
ComboBox7.Clear
ComboBox7.AddItem "Baby Juice"
ComboBox7.AddItem "Cordial"
ComboBox7.AddItem "Fruit Shoot"
ComboBox7.AddItem "J2O"
ComboBox7.AddItem "NRB"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "PostMix"
ComboBox7.AddItem "Red Bull"
ComboBox7.AddItem "Slush"
ComboBox7.AddItem "Water"
End If
'It's a Multi Buy'
If ComboBox6 = "Multi Buys" Then
ComboBox7.Clear
ComboBox7.AddItem "Becks"
ComboBox7.AddItem "Budweiser"
ComboBox7.AddItem "Corkys"
ComboBox7.AddItem "Fosters"
ComboBox7.AddItem "J20"
ComboBox7.AddItem "Kronenbourg"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Strongbow"
ComboBox7.AddItem "VK"
End If
'It's a PPL'
If ComboBox6 = "PPL" Then
ComboBox7.Clear
ComboBox7.AddItem "Becks"
ComboBox7.AddItem "Budweiser"
ComboBox7.AddItem "Corona"
ComboBox7.AddItem "Fosters"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Stella"
End If
'It's a PPS'
If ComboBox6 = "PPS" Then
ComboBox7.Clear
ComboBox7.AddItem "Smirnoff Ice"
ComboBox7.AddItem "VK"
End If
'It's Spirits'
If ComboBox6 = "Spirits" Then
ComboBox7.Clear
ComboBox7.AddItem "Brandy"
ComboBox7.AddItem "Corkys"
ComboBox7.AddItem "Gin"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Rum"
ComboBox7.AddItem "Schnapps"
ComboBox7.AddItem "Vodka"
ComboBox7.AddItem "Whiskey"
End If
'It's Sundries'
If ComboBox6 = "Sundries" Then
ComboBox7.Clear
ComboBox7.AddItem "Crisps"
ComboBox7.AddItem "Nuts"
ComboBox7.AddItem "Snacks"
End If
'It's Wine'
If ComboBox6 = "Wine" Then
ComboBox7.Clear
ComboBox7.AddItem "Champagne"
ComboBox7.AddItem "Party"
ComboBox7.AddItem "Red"
ComboBox7.AddItem "Red SSB"
ComboBox7.AddItem "Rose"
ComboBox7.AddItem "White"
ComboBox7.AddItem "White SSB"
End If
End Sub
Hope you can help as you can probably tell I'm not fluent in this stuff.

