Multiple dependant comboboxes with repeating list

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
60
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,012
Office Version
365
Platform
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 />
 

Forum statistics

Threads
1,081,617
Messages
5,360,044
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top