Multiple dependant comboboxes with repeating list

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
76
Hi,

I presently have a coding that works but it is extremely long and I would like to know if there is an easier way (I am presuming yes)

Present set up
ComboBox1(ListProv1) -select a Province / ComboBox2(ListCity1) -select a City
the list of cities will depend on the selection in Combobox1

this repeats up to (ListProv10)

here is the present coding:


Code:
Private Sub ListProv1_Change()
Dim index As Integer
 index = ListProv1.ListIndex
 
 ListCity1.Clear
 
Select Case index
     Case Is = 0
         With ListCity1
             .AddItem "Balzac"
             .AddItem "Brooks"
             .AddItem "Calgary"
             .AddItem "Coutts"
             .AddItem "Edmonton"
             .AddItem "Fort Macleod"
End With
     Case Is = 1
         With ListCity1
             .AddItem "Abbotsford"
             .AddItem "Agassiz"
             .AddItem "Armstrong"
             .AddItem "Burnaby"
             .AddItem "Chilliwack"
             .AddItem "Cloverdale"
             .AddItem "Coquitlam"
End With
     Case Is = 2
         With ListCity1
             .AddItem "Blumenort"
             .AddItem "Boissevain"
             .AddItem "Brandon"
             .AddItem "Carman"
             .AddItem "Dauphin"
             .AddItem "Emerson"
End With
     Case Is = 3
         With ListCity1
             .AddItem "Blacks Harbour"
             .AddItem "Clair"
             .AddItem "Edmunston"
             .AddItem "Florenceville"
             .AddItem "Fredericton"
             .AddItem "GrandFalls/Grand Sault"
             .AddItem "Moncton"
             .AddItem "Saint John"
             .AddItem "Shediac"
             .AddItem "Shippigan"
             .AddItem "St François"
             .AddItem "ST George"
             .AddItem "Woodstock"
End With
     Case Is = 4
         With ListCity1
             .AddItem "Bay Bulls"
             .AddItem "Bonavista"
             .AddItem "Brig Bay"
             .AddItem "Clarenville"
             .AddItem "Clarke's Beach"
             .AddItem "Corner Brook"
             .AddItem "Dildo"
             .AddItem "Glovertown"
End With
     Case Is = 5
         With ListCity1
             .AddItem "Antigonish"
             .AddItem "Berwick"
             .AddItem "Bible Hill"
             .AddItem "Bridgewater"
             .AddItem "Dartmouth"
             .AddItem "Digby"
             .AddItem "Halifax"
End With
     Case Is = 6
         With ListCity1
             .AddItem "Cambridge Bay"
             .AddItem "Rankin Inlet"
End With
     Case Is = 7
         With ListCity1
             .AddItem "Amherstburg"
             .AddItem "Barrie"
             .AddItem "Beamsvill"
             .AddItem "Belleville"
             .AddItem "Bradford"
             .AddItem "Bramalea"
End With         
     Case Is = 8
         With ListCity1
             .AddItem "Albany"
             .AddItem "Borden-Carleton"
             .AddItem "Charlottetown"
             .AddItem "Montague"
             .AddItem "Morell"
             .AddItem "Souris"
             .AddItem "O'Leary"
             .AddItem "Summerside"
End With    
     Case Is = 9
         With ListCity1
             .AddItem "Alma"
             .AddItem "Ange-Gardien"
             .AddItem "Anjou"
             .AddItem "Asbestos"
             .AddItem "Baie Comeau"
             .AddItem "Berthierville"
             .AddItem "Blainville"
 End With
     Case Is = 10
         With ListCity1
             .AddItem "Battleford"
             .AddItem "Carlyle"
             .AddItem "Duck Lake"
             .AddItem "Melfort"
             .AddItem "Moose Jaw"
End With
End Select
End Sub
Private Sub ListProv2_Change()
Dim index1 As Integer
 index1 = ListProv2.ListIndex
 
 ListCity2.Clear
 
Select Case index1
  Case Is = 0
         With ListCity2
             .AddItem "Balzac"
             .AddItem "Brooks"

and the whole thing restarts with the same cities ListProv10


As I said, I presently have it working but iy is very long (I also didn't list all the cities)



can you please provide some assistance?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
One option would be to do it like
Code:
Dim Dic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value)
End Sub

Private Sub UserForm_Initialize()
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.Add "province1", Array("Balzac", "Brooks", "Calgary", "Coutts")
   Dic.Add "Province2", Array("Abbots", "Agassiz", "Burnaby")
   
   Me.ComboBox1.List = Dic.Keys
End Sub
But a simpler approach would be if you have the Provinces/cities on a sheet along the lines of

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">County</td><td style=";">Ward</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Hertfordshire</td><td style=";">St Peters</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Hertfordshire</td><td style=";">Ashley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Hertfordshire</td><td style=";">Hatfield Central</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Hertfordshire</td><td style=";">Watling</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Somerset</td><td style=";">Abbey</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Somerset</td><td style=";">Bruton</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Somerset</td><td style=";">Frome Market</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Staffordshire</td><td style=";">Castle</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Staffordshire</td><td style=";">Fazeley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Warwickshire</td><td style=";">Water Orton</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Warwickshire</td><td style=";">Bidford West & Salford</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Warwickshire</td><td style=";">Arden</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">West Midlands</td><td style=";">Ladywood</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">West Midlands</td><td style=";">Smith's Wood</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">West Midlands</td><td style=";">Newton</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">West Midlands</td><td style=";">Halesowen North</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">West Midlands</td><td style=";">Streetly</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Worcestershire</td><td style=";">Wythall West</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Worcestershire</td><td style=";">Inkberrow</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Worcestershire</td><td style=";">Central</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">All</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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
Top