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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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
 
Upvote 0
What does your data look like? Is it something like this?


Excel 2013/2016
AB
1CountyDistrict
2BedfordshireLuton
3BedfordshireCentral Bedfordshire
4BedfordshireLuton
5BedfordshireCentral Bedfordshire
6BedfordshireCentral Bedfordshire
7BedfordshireBedford
8BedfordshireBedford
9BedfordshireCentral Bedfordshire
10BedfordshireBedford
11BerkshireBracknell Forest
12BerkshireBracknell Forest
13BerkshireWokingham
14BerkshireWest Berkshire
15BerkshireWest Berkshire
16BerkshireWest Berkshire
17BerkshireReading
18BerkshireReading
19BuckinghamshireWycombe
20BuckinghamshireSouth Bucks
21BuckinghamshireWycombe
22BuckinghamshireChiltern
23BuckinghamshireChiltern
Engine
 
Upvote 0
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>
 
Upvote 0
How will you select either a region or county, is there is no data?
 
Upvote 0
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.
 
Upvote 0
Is the data on you cover page arranged in the same way as the sample I showed in post#4?
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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