VBA- Multiple selection dependent list

[OK21294]

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]

Hi & welcome to the board.
How about something like

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]

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:

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]

What does your data look like? Is it something like this?

Excel 2013/2016
	A	B
1	County	District
2	Bedfordshire	Luton
3	Bedfordshire	Central Bedfordshire
4	Bedfordshire	Luton
5	Bedfordshire	Central Bedfordshire
6	Bedfordshire	Central Bedfordshire
7	Bedfordshire	Bedford
8	Bedfordshire	Bedford
9	Bedfordshire	Central Bedfordshire
10	Bedfordshire	Bedford
11	Berkshire	Bracknell Forest
12	Berkshire	Bracknell Forest
13	Berkshire	Wokingham
14	Berkshire	West Berkshire
15	Berkshire	West Berkshire
16	Berkshire	West Berkshire
17	Berkshire	Reading
18	Berkshire	Reading
19	Buckinghamshire	Wycombe
20	Buckinghamshire	South Bucks
21	Buckinghamshire	Wycombe
22	Buckinghamshire	Chiltern
23	Buckinghamshire	Chiltern
Engine

 

[OK21294]

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;">

County	District
Bedfordshire	Luton
Bedfordshire	Central Bedfordshire
Bedfordshire	Luton

<thead>
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

</body>

 

[Fluff]

How will you select either a region or county, is there is no data?

 

[OK21294]

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]

Is the data on you cover page arranged in the same way as the sample I showed in post#4?

 

[OK21294]

Hello,

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

[TABLE="width: 640"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Regions[/TD]
[TD]SouthEast[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]South East[/TD]
[TD]Bracknell Forest[/TD]
[TD]Camden[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]Brighton and Hove[/TD]
[TD]Royal Borough of Greenwich[/TD]
[/TR]
[TR]
[TD]North West[/TD]
[TD]Buckinghamshire[/TD]
[TD]Hackney[/TD]
[/TR]
[TR]
[TD]East of England[/TD]
[TD]East Sussex[/TD]
[TD]Hammersmith and Fulham[/TD]
[/TR]
[TR]
[TD]West Midlands[/TD]
[TD]Hampshire[/TD]
[TD]Islington[/TD]
[/TR]
[TR]
[TD]South West[/TD]
[TD]Isle of Wight[/TD]
[TD]Royal Borough of Kensington and Chelsea[/TD]
[/TR]
[TR]
[TD]Yorkshire [/TD]
[TD]Kent[/TD]
[TD]Lambeth[/TD]
[/TR]
[TR]
[TD]East Midlands[/TD]
[TD]Medway[/TD]
[TD]Lewisham[/TD]
[/TR]
[TR]
[TD]North East[/TD]
[TD]Milton Keynes[/TD]
[TD]Southwark[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

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

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