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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. 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
56,614
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
56,614
Office Version
  1. 365
Platform
  1. Windows
How will you select either a region or county, is there is no data?
 

OK21294

New Member
Joined
Aug 1, 2018
Messages
9

ADVERTISEMENT

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
56,614
Office Version
  1. 365
Platform
  1. 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
56,614
Office Version
  1. 365
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

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