Clear unactive combo boxes

[cfatz1]

Hi-

I have 10 combo boxes that filter a report that is in a seperate sheet.
How can I clear all values that have been chosen in the non-active
combos so that each time the user selects the "Sort" Sheet they are
presented with blank combo boxes....EXCEPT for the one they just sorted
on? As of now, if I select one and then go back to the sort sheet, the
value I selected is still there for multiple selections that were made
in the past and is confusing to the user who wants to sort off only one
combo box. Each of these combos are independent of the others....you
can only sort by one.

I currently have this code on each of the Change commands for the
macros: (Note: I leave out the Active Combo box from the code but it
still clears that combo as well. The below example is for ComboBox1)

ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
ComboBox5.Text = ""
ComboBox6.Text = ""
ComboBox7.Text = ""
ComboBox8.Text = ""
ComboBox9.Text = ""
ComboBox10.Text = ""

Hope this is clear! Thanks for helping me out.

-Chris

 

[Tom Ogilvy]

I suspect when you clear combox 2 in your example, its change event fires
and clears combobox1. You need to put in a boolean variable at the top of
the module

Public bBlockEvents as Boolean

then in each of your change events, check the state of that variable as the
first thing

Private Sub Combobox1_change()
if bBlockEvents = True then
exit sub
end if
bBlockEvents = True
ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
ComboBox5.Text = ""
ComboBox6.Text = ""
ComboBox7.Text = ""
ComboBox8.Text = ""
ComboBox9.Text = ""
ComboBox10.Text = ""

' other code

bBlockEvents = False
End Sub

 

[cfatz1]

Tom-

This completely shuts off my combo boxes when the value is selected.
Nothing happens at all.

Any ideas?

-Chris