Change the text of a worksheet activeX ComboBox without triggering the change event code associated with it.

[potter_ricky]

Hi, I’ll try to explain this as best I can. On a spreadsheet I have three activeX controls, 2 ListBoxes (Continent & Country) and one ComboBox (Groups). Both ListBoxes are and need to be multi select.

The Continent ListBox has code for its change event to only add Countries in the Country ListBox that have been defined as linked to the Continent (e.g. the Country Thailand is linked to the Continent South-Eastern Asia).

The ComboBox is for user defined country groups, by default its text is set to “No Selected Group”. This has a change event linked to it which will

1.) Select all Continents and Countries as per the defined group, and as result of the Continent ListBox change event add all related Countries into the Country list box.

2.) Select only Countries in the Country ListBox that are defined in the group

HERE IS MY PROBLEM:

Should a group be used and then a further Continent(s) and/or Country(s) be selected/deselected I would like to change the text in the ComboBox back to default “No Selected Group”.

I was thinking of putting a switch in (making a cell on the worksheet read 1 or 0 and then reading this in the change events to determine whether or not to execute) but discovered that the click event on a multi select ListBox is disabled and that it would appear that a change event executes first before a click event.

Any comments suggestions would be most welcome, an example of my code is below and I've uploaded a example of the workbook to fileape which would probably be easier to understand: http://fileape.com/dl/u2k4epT4DyohidzP

Thanks,

Rick
Excel 2007 Win7 Ultimate 64x

Sheet1(Sheet1) Code;

Private Sub ComboBox1_Change()

Dim Count As Integer
Dim ListCount As Integer 'To count items in listbox

If Worksheets("Sheet1").ComboBox1.Text = "No Selected Group" Then

    Else
        'De select all sub continents
        Count = Worksheets("Sheet1").ListBox1.ListCount
        Do Until Count = 0
            Worksheets("Sheet1").ListBox1.Selected(Count - 1) = False
            Count = Count - 1
        Loop
    
        'Select sub Continents for selected group
        Count = 2
        Do Until Worksheets("Sheet1").Cells(Count, 10).Value = ""
            
            If Worksheets("Sheet1").Cells(Count, 9).Value = Worksheets("Sheet1").ComboBox1.Text Then
                ListCount = Worksheets("Sheet1").ListBox1.ListCount
                Do Until ListCount = 0
                    If Worksheets("Sheet1").ListBox1.List(ListCount - 1) = Worksheets("Sheet1").Cells(Count, 10).Value Then
                        Worksheets("Sheet1").ListBox1.Selected(ListCount - 1) = True
                    End If
                    ListCount = ListCount - 1
                Loop
            End If
        Count = Count + 1
        Loop
        
        'De select all countries
        Count = Worksheets("Sheet1").ListBox2.ListCount
        Do Until Count = 0
            Worksheets("Sheet1").ListBox2.Selected(Count - 1) = False
            Count = Count - 1
        Loop
        
        'Select sub Countries for selected group
        Count = 2
        Do Until Worksheets("Sheet1").Cells(Count, 7).Value = ""
                    
            If Worksheets("Sheet1").Cells(Count, 6).Value = Worksheets("Sheet1").ComboBox1.Text Then
                ListCount = Worksheets("Sheet1").ListBox2.ListCount
                Do Until ListCount = 0
                    If Worksheets("Sheet1").ListBox2.List(ListCount - 1) = Worksheets("Sheet1").Cells(Count, 7).Value Then
                        Worksheets("Sheet1").ListBox2.Selected(ListCount - 1) = True
                    End If
                    ListCount = ListCount - 1
                Loop
            End If
        Count = Count + 1
        Loop
        
End If

End Sub

Private Sub ListBox1_Change()

Application.ScreenUpdating = False

'Clear temp table
Range(Cells(2, 12), Cells(20, 13)).ClearContents

'Check all items in ListBox1
Dim Count As Integer
Dim RowCount As Integer 'Used to count temp table entering rows

Count = Worksheets("Sheet1").ListBox1.ListCount
RowCount = 2
Do Until Count = 0
    'If item is selected then add it to temp table 1
    If Worksheets("Sheet1").ListBox1.Selected(Count - 1) = True Then
        Worksheets("Sheet1").Cells(RowCount, 12).Value = Worksheets("Sheet1").ListBox1.List(Count - 1)
        RowCount = RowCount + 1
    End If
    Count = Count - 1
Loop

'Find all countries assocaited with selected Subcontinents
Dim SelectedCount As Integer 'Used to count temp table entered rows

SelectedCount = 2
Count = 2
RowCount = 2
'Check each sub contienent against...
Do Until Worksheets("Sheet1").Cells(SelectedCount, 12).Value = ""
    '...each country
    Do Until Worksheets("Sheet1").Cells(Count, 3).Value = ""
    'Enter Countries for associated selected sub continents into temp table 2
    If Worksheets("Sheet1").Cells(Count, 4).Value = Worksheets("Sheet1").Cells(SelectedCount, 12).Value Then
        Worksheets("Sheet1").Cells(RowCount, 13).Value = Worksheets("Sheet1").Cells(Count, 3).Value
        RowCount = RowCount + 1
    End If
    Count = Count + 1
    Loop
    SelectedCount = SelectedCount + 1
    Count = 2
Loop

'Sort Temp table 2 alphebtically
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range(Cells(2, 13), Cells(RowCount - 1, 13))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Worksheets("Sheet1").Cells(1, 1).Select

'Remove all items from ListBox2
Count = Worksheets("Sheet1").ListBox2.ListCount
Do Until Count = 0
    Worksheets("Sheet1").ListBox2.RemoveItem Count - 1
    Count = Count - 1
Loop

'Add Temp 2 items to list box 2
Count = 2
Do Until Worksheets("Sheet1").Cells(Count, 13).Value = ""
    Worksheets("Sheet1").ListBox2.AddItem (Cells(Count, 13).Value)
    Count = Count + 1
Loop

'Select all items in List Box 2
Count = Worksheets("Sheet1").ListBox2.ListCount
Do Until Count = 0
    Worksheets("Sheet1").ListBox2.Selected(Count - 1) = True
    Count = Count - 1
Loop

'Clear temp table
Range(Cells(2, 12), Cells(20, 13)).ClearContents

Application.ScreenUpdating = True

End Sub

ThisWorkBook Code:

Private Sub Workbook_Open()

Dim Count As Integer 'Counter for loading items into controls

'Load ListBox1 items
Count = 2
Do Until Worksheets("Sheet1").Cells(Count, 1).Value = ""
    Worksheets("Sheet1").ListBox1.AddItem (Cells(Count, 1).Value)
    Count = Count + 1
Loop

'Load ListBox2 items
Count = 2
Do Until Worksheets("Sheet1").Cells(Count, 3).Value = ""
    Worksheets("Sheet1").ListBox2.AddItem (Cells(Count, 3).Value)
    Count = Count + 1
Loop

'Load combobox items items
Worksheets("Sheet1").ComboBox1.AddItem ("No Selected Group")
Worksheets("Sheet1").ComboBox1.AddItem ("Group1")
Worksheets("Sheet1").ComboBox1.AddItem ("Group2")
Worksheets("Sheet1").ComboBox1.Text = Worksheets("Sheet1").ComboBox1.List(0)

'Select all items in list box 1
Count = Worksheets("Sheet1").ListBox1.ListCount
Do Until Count = 0
    Worksheets("Sheet1").ListBox1.Selected(Count - 1) = True
    Count = Count - 1
Loop

End Sub

(Comma indicates new row)
On Sheet1 starting in row1

Column A Contains:
Sub Continents, Australia and New Zealand, South-Eastern Asia, Northern America

Column C Contains:
Countries, Australia, New Zealand, Norfolk Island, Brunei, Cambodia, Indonesia, Laos, Malaysia, Myanmar [Burma], Philippines, Singapore, Thailand, Timor-Leste, Vietnam, Bermuda, Canada, Greenland, Saint Pierre and Miquelon

Column D Contains:
Sub Continents, Australia and New Zealand, Australia and New Zealand, Australia and New Zealand, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, South-Eastern Asia, Northern America, Northern America, Northern America, Northern America

Column F Contains:
Group, Group1, Group1, Group2, Group2

Column G Contains:
Countries, Australia, Indonesia, Singapore, Thailand

Column I Contains:
Group, Group1, Group1, Group2

Column J Contains:
Sub Continents, Australia and New Zealand, South-Eastern Asia, South-Eastern Asia

Column L Contains:
Temp1

Column M Contains:
Temp2

 

[RoryA]

The principle is correct (though I would use a variable in your code, rather than a cell) - you need the Change event of the listbox rather than the click event.