VBA- Multiple selection dependent list

OK21294

New Member
Joined
Aug 1, 2018
Messages
9
Hello,

I am creating a data base in which I need to control the entries for two separate categories. e.g. Regions and counties.

I have a column for Regions and a column for counties.

My end goal is to have a Userform or list for Regions which pops up and lists all the regions in the UK, and for the user to be able to select multiple regions. After the Regions have been chosen, I would like the user to be able to select multiple counties based on whatever region/s have been chosen.

For example, if the user was to choose South East and Greater London, all the counties/boroughs in the South East and London Boroughs would be shown in the drop down list for Counties.

So far I have got the Userform to select the Regions but have am struggling to do the rest- any help or suggestions would be very much appreciated!

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,335
Office Version
365
Platform
Windows
Hi & welcome to the board.
How about something like
Code:
Option Explicit
Dim Dic As Object

Private Sub CommandButton1_Click()
   Dim Lst As Variant
   Dim i As Long
   For i = 0 To Me.ListBox1.ListCount - 1
      If Me.ListBox1.Selected(i) Then
         Lst = Lst & Join(Dic(Me.ListBox1.List(i)).keys, "|") & "|"
      End If
   Next i
   Me.ListBox2.List = Split(Lst, "|")
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("pcode")
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
   For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         Dic.Add Cl.Value, CreateObject("scripting.dictionary")
         Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
      ElseIf Not Dic(Cl.Value).exists(Cl.Offset(, 1).Value) Then
         Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
      End If
   Next Cl
   Me.ListBox1.List = Dic.keys
End Sub
 

OK21294

New Member
Joined
Aug 1, 2018
Messages
9
Hello,

Thanks for your reply.

I am really new to VBA . Would the code you have given be pasted into the WorkSheet where I would like the Userform's to pop up?

So far I have added this code to the Region Userform Visual Basis:

Code:
Private Sub ListBox1_Click()


End Sub


Private Sub UserForm_Initialize()


    With ListBox1
        .List = Range("Cover!B3:B8").Value    'Range of cells with the list of Regions
        .MultiSelect = fmMultiSelectMulti
    End With
    
    UserForm1.Caption = "Select Regions"
    CommandButton1.Caption = "Select"
    
End Sub




Private Sub CommandButton1_Click()


    Dim i As Long, strTemp As String
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then strTemp = strTemp & .List(i) & ", "
        Next i
    End With
    If Len(strTemp) Then
        strTemp = Left(strTemp, Len(strTemp) - 2)
        ActiveCell.Value = strTemp
    End If
    Unload Me
    
End Sub
[Code]

This code works for the Region Userform however I am unsure how to make the second County Userform pop up. 

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,335
Office Version
365
Platform
Windows
What does your data look like? Is it something like this?

<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=";">District</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Bedfordshire</td><td style=";">Luton</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Bedfordshire</td><td style=";">Central Bedfordshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Bedfordshire</td><td style=";">Luton</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Bedfordshire</td><td style=";">Central Bedfordshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Bedfordshire</td><td style=";">Central Bedfordshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Bedfordshire</td><td style=";">Bedford</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Bedfordshire</td><td style=";">Bedford</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Bedfordshire</td><td style=";">Central Bedfordshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Bedfordshire</td><td style=";">Bedford</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Berkshire</td><td style=";">Bracknell Forest</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Berkshire</td><td style=";">Bracknell Forest</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Berkshire</td><td style=";">Wokingham</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Berkshire</td><td style=";">West Berkshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Berkshire</td><td style=";">West Berkshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Berkshire</td><td style=";">West Berkshire</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Berkshire</td><td style=";">Reading</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Berkshire</td><td style=";">Reading</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Buckinghamshire</td><td style=";">Wycombe</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Buckinghamshire</td><td style=";">South Bucks</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Buckinghamshire</td><td style=";">Wycombe</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Buckinghamshire</td><td style=";">Chiltern</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">Buckinghamshire</td><td style=";">Chiltern</td></tr></tbody></table><p style="width:4.8em;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)">Engine</p><br /><br />
 

OK21294

New Member
Joined
Aug 1, 2018
Messages
9
Hello,

I don't have any data yet, this is going to be used as a data entry form where you select the regions you have lived and the counties you have lived in.

But hopefully at the end it should look like how you have outlined above however in both columns, there may be multiple entries e.g. Buckinghamshire and Berkshire.

Thanks

Olivia


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
AB
1CountyDistrict
2BedfordshireLuton
3BedfordshireCentral Bedfordshire
4BedfordshireLuton

<thead>
</thead><tbody>
</tbody>
</body>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,335
Office Version
365
Platform
Windows
How will you select either a region or county, is there is no data?
 

OK21294

New Member
Joined
Aug 1, 2018
Messages
9
I have various lists of the regions and county's on a cover page. I have created a userform which when clicking in the Data Entry worksheet Region column, it brings up the first UserForm in which the user can select multiple regions (linked to the region list on the cover page). I then am attempting to have another UserForm pop up when clicking on the County column which will show a list of the counties based on which ever regions have been selected in the Region column.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,335
Office Version
365
Platform
Windows
Is the data on you cover page arranged in the same way as the sample I showed in post#4?
 

OK21294

New Member
Joined
Aug 1, 2018
Messages
9
Hello,

Yeah its arranged in a similar way, please see below for example:

RegionsSouthEastLondon
South EastBracknell ForestCamden
LondonBrighton and HoveRoyal Borough of Greenwich
North WestBuckinghamshireHackney
East of EnglandEast SussexHammersmith and Fulham
West MidlandsHampshireIslington
South WestIsle of WightRoyal Borough of Kensington and Chelsea
Yorkshire KentLambeth
East MidlandsMedwayLewisham
North EastMilton KeynesSouthwark

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,335
Office Version
365
Platform
Windows
Set each region as a named range, ie for the ones you've shown theyd be "Regions", "SouthEast" & "London"
Then create a userform with 2 listboxes & a commandbutton & use
Code:
Private Sub CommandButton1_Click()
   Dim Lst As Variant
   Dim i As Long, v As String
   For i = 0 To Me.ListBox1.ListCount - 1
      If Me.ListBox1.Selected(i) Then
         v = Replace(Me.ListBox1.List(i), " ", "")
         Lst = Lst & Join(Application.Transpose(Range(v).Value), "|") & "|"
      End If
   Next i
   Me.ListBox2.List = Split(Lst, "|")
End Sub

Private Sub UserForm_Initialize()
   Me.ListBox1.List = Range("Regions").Value
End Sub
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top