Clicked value on listbox depending on the other listbox

lucausa75

New Member
Joined
Oct 15, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
Dear all,
I have an userform in excel that has two listboxes.
The first one called 'lstRegion' displays the list of 3 regions (Region AA, BENELUX, Region C) which group different countries.
The second list box called 'lstCountry' displays some countries which can be part of one of the region in 'lstRegion'.
The user can select/deselect multiple records in 'lstRegion' and automatically, by the Change event, the countries belonging to the selected country will get selected/deselected.
The above features works by the below code:

VBA Code:
Private Sub lstRegion_Change()
    Dim i As Long
    
    lstRegionChangedList = True
    lstCountryChangedList = False
    SelectedItemsCountRegion = 0
    
    If lstCountryChangedList = False Then
        SelectDeselectCountryFromRegion lstRegion, lstCountry
        lstRegionChangedList = False
    End If
    
    For i = 0 To lstRegion.ListCount - 1
        If lstRegion.Selected(i) = True Then
            SelectedItemsCountRegion = SelectedItemsCountRegion + 1
'            If SelectedItemsCountRegion > 0 Then
'                Exit For
'            End If
        End If
    Next i
    
    If SelectedItemsCountRegion = 0 Then
        chkRegion_Select_Deselect_All.Caption = "SELECT ALL"
        chkRegion_Select_Deselect_All.Value = False
    ElseIf SelectedItemsCountRegion = lstRegion.ListCount Then
        chkRegion_Select_Deselect_All.Value = True
    End If
    
    If SelectedItemsCountRegion > 0 And SelectedItemsCountCountry > 0 Then
        cmdFind.Enabled = True
    Else
        cmdFind.Enabled = False
    End If
    
End Sub

Private Sub chkCountry_Select_Deselect_All_Click()
    chkCountry_Select_Deselect_All.Caption = IIf(chkCountry_Select_Deselect_All.Value = True, "DESELECT ALL", "SELECT ALL")
    cmdFind.Enabled = chkCountry_Select_Deselect_All.Value
    SelectDeselectAllItemsListBox lstCountry, chkCountry_Select_Deselect_All.Value
End Sub

Now, what I would like to do, Is also the way around: user can also select/deselect one or more countries and automatically the related region will get selected/deselected

For you reference you can download the file from here in order to discuss with you have to fix

Thankx
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,214,978
Messages
6,122,549
Members
449,089
Latest member
davidcom

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