Multiple ComboBox's

H

HvyEquipment

New Member
Joined
Apr 19, 2004
Messages
11
OK, everyone, I know that this has come up repeatedly, and I've read most of the posts regarding it, but I still need some help (and Data Validation just isn't the right format for my application). I need to be able to Cascade three consecutive ComboBox's in a row, with the 2nd and 3rd invisible until a selection is made from the one previous to it.

The 2nd and 3rd ComboBox lists are dependent upon the previous ComboBox selection.

To top it all off, I need the 3rd and final selection to 'jump' to Cell A1 in a specific Worksheet associated with the cascading selection.

For Example:
ComboBox1 will always list: Sales, Service, Safety

ComboBox2 will list: Sales1, Sales2, Sales3 (if the 1st = 'Sales')
ComboBox2 will list: Service1, Service2, Service3 (if the 1st = 'Service')
ComboBox2 will list: Safety1, Safety2, Safety3 (if the 1st = 'Safety')

ComboBox3 will list: Sales1-1, Sales1-2 (if the 1st = 'Sales' & the 2nd = 'Sales1')
ComboBox3 will list: Sales2-1, Sales2-2 (if the 1st = 'Sales' & the 2nd = 'Sales2')
etc.

The lists are simplified for explanatory purposes, but will vary and cannot be mathematically calculated.

Once the 3rd selection has been made, it would need to jump to a specific Worksheet, Cell A1 (I've been able to make the 'jump' work, it's the cascading Combobox's that give me fits).

I am not new to programming...just old school and not up on VB (am I giving away my age?).

Seriously, any help would be greatly appreciated and sorry for the long post.
 
Sort by date Sort by votes
create a userform with 3 combobox

Private Sub ComboBox1_Change()
If Not ComboBox1 = "" Then
ComboBox2.Enabled = True: ComboBox2.BackColor = &HFFFF&
ComboBox3.Enabled = True: ComboBox3.BackColor = &HFFFF&
End If

End Sub



this will sort out you to enable combo 2 and 3 after a value has been picked from list

to get the value to go to specific cell use

sheet("whatever is called").Cells(1,1)= combobox3.value


hope this helps
:-D
 
Upvote 0
That was helpful, but still doesn't help me with my cascading ComboBox's. I need to populate the 2nd and 3rd ComboBox's with defined lists based on what was selected in the ComboBox before it. Thanks Phonio! Can anyone help with the cascading ComboBoxes?
 
Upvote 0
Hi HvyEquipment

If it's a UserForm solution your looking for, create a UserForm with 3 Comboboxes and try this:

Code: 
Private Sub UserForm_Activate()
    'Set AddItem for Combobox1
    ComboBox1.AddItem "Sales"
    ComboBox1.AddItem "Service"
    ComboBox1.AddItem "Safety"
    'Set Visibilty for Combobox 2 - 3
    ComboBox2.Visible = False
    ComboBox3.Visible = False
End Sub

Private Sub ComboBox1_Change()
    'Combobox1 = "" Combobox2 = Hidden
    If ComboBox1 = "" Then
    ComboBox2.Visible = False     'Hidden
    End If
   
    'Combobox Sales
    If ComboBox1 = "Sales" Then
    ComboBox2.Visible = True      'Visible
    ComboBox2.Clear               'Clear Combobox
    ComboBox2.AddItem "Sales1"    'ComboBox AddItem
    ComboBox2.AddItem "Sales2"    'ComboBox AddItem
    ComboBox2.AddItem "Sales3"    'ComboBox AddItem
    End If
    
    'Combobox Service
    If ComboBox1 = "Service" Then
    ComboBox2.Visible = True      'Visible
    ComboBox2.Clear               'Clear Combobox
    ComboBox2.AddItem "Service1"  'ComboBox AddItem
    ComboBox2.AddItem "Service2"  'ComboBox AddItem
    ComboBox2.AddItem "Service3"  'ComboBox AddItem
    End If
    
    'Combobox Safety
    If ComboBox1 = "Safety" Then
    ComboBox2.Visible = True      'Visible
    ComboBox2.Clear               'Clear Combobox
    ComboBox2.AddItem "Safety1"   'ComboBox AddItem
    ComboBox2.AddItem "Safety2"   'ComboBox AddItem
    ComboBox2.AddItem "Safety3"   'ComboBox AddItem
    End If
End Sub

Private Sub ComboBox2_Change()
    'Combobox2 = "" Combobox3 = Hidden
    If ComboBox2 = "" Then
    ComboBox3.Visible = False     'Hidden
    End If
    
    'Combobox1 = "Sales" Combobox2 = "Sales1"
    If ComboBox1 = "Sales" Then
    If ComboBox2 = "Sales1" Then
    ComboBox3.Visible = True      'Visible
    ComboBox3.Clear               'Clear Combobox
    ComboBox3.AddItem "Sales1-1"  'ComboBox AddItem
    ComboBox3.AddItem "Sales1-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Sales" Combobox2 = "Sales2"
    If ComboBox1 = "Sales" Then
    If ComboBox2 = "Sales2" Then
    ComboBox3.Visible = True      'Visible
    ComboBox3.Clear               'Clear Combobox
    ComboBox3.AddItem "Sales2-1"  'ComboBox AddItem
    ComboBox3.AddItem "Sales2-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Sales" Combobox2 = "Sales3"
    If ComboBox1 = "Sales" Then
    If ComboBox2 = "Sales3" Then
    ComboBox3.Visible = True      'Visible
    ComboBox3.Clear               'Clear Combobox
    ComboBox3.AddItem "Sales3-1"  'ComboBox AddItem
    ComboBox3.AddItem "Sales3-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Service" Combobox2 = "Service1"
    If ComboBox1 = "Service" Then
    If ComboBox2 = "Service1" Then
    ComboBox3.Visible = True        'Visible
    ComboBox3.Clear                 'Clear Combobox
    ComboBox3.AddItem "Service1-1"  'ComboBox AddItem
    ComboBox3.AddItem "Service1-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Service" Combobox2 = "Service2"
    If ComboBox1 = "Service" Then
    If ComboBox2 = "Service2" Then
    ComboBox3.Visible = True        'Visible
    ComboBox3.Clear                 'Clear Combobox
    ComboBox3.AddItem "Service2-1"  'ComboBox AddItem
    ComboBox3.AddItem "Service2-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Service" Combobox2 = "Service3"
    If ComboBox1 = "Service" Then
    If ComboBox2 = "Service3" Then
    ComboBox3.Visible = True        'Visible
    ComboBox3.Clear                 'Clear Combobox
    ComboBox3.AddItem "Service3-1"  'ComboBox AddItem
    ComboBox3.AddItem "Service3-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Safety" Combobox2 = "Safety1"
    If ComboBox1 = "Safety" Then
    If ComboBox2 = "Safety1" Then
    ComboBox3.Visible = True
    ComboBox3.Clear                'Clear Combobox
    ComboBox3.AddItem "Safety1-1"  'ComboBox AddItem
    ComboBox3.AddItem "Safety1-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Safety" Combobox2 = "Safety2"
    If ComboBox1 = "Safety" Then
    If ComboBox2 = "Safety2" Then
    ComboBox3.Visible = True       'Visible
    ComboBox3.Clear                'Clear Combobox
    ComboBox3.AddItem "Safety2-1"  'ComboBox AddItem
    ComboBox3.AddItem "Safety2-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
    
    'Combobox1 = "Safety" Combobox2 = "Safety3"
    If ComboBox1 = "Safety" Then
    If ComboBox2 = "Safety3" Then
    ComboBox3.Visible = True       'Visible
    ComboBox3.Clear                'Clear Combobox
    ComboBox3.AddItem "Safety3-1"  'ComboBox AddItem
    ComboBox3.AddItem "Safety3-2"  'ComboBox AddItem
    'Sheets and Range Select
    Sheets("Your_Sheets_Name_Here").Range("A1").Select
    End If
    End If
End Sub

Hope it get's you started...

/Roger
 
Upvote 0
That worked great! I had to make one change, though:

'Sheets and Range Select
Sheets("S0001").Select
Range("A1").Select

For some reason, the Sheets("S0001").Range("A1").Select returned an error...

I use this forum quite regularly (even though I just registered) and am constantly amazed at how well the site works! Without a doubt, one of the best sites of it's kind on the Web (and I've Looked!).

Thanks Stromma, your help was invaluable!
 
Upvote 0
Hi HvyEquipment

Glad it worked!

I'm one of those who often requires more help than i can give, so it's quit nice to do something the other way around for a change.

Take Care!

/Roger
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
2K
MissDB101
M
C
  • Question Question
Excel VBA- Viewing and Updating existing worksheet data with Excel UserForm with Dependent Comboboxes
Replies
2
Views
1K
charliechaz
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
K
  • Question Question
help with a combobox and how it loads its list (its selections)
2
Replies
13
Views
1K
Anthony47
A
R
  • Question Question
Userform issue/challenge - Copying listbox selection to a sheet
Replies
0
Views
909
Ronie
R

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top