Combo Box Trigger

[rudevincy]

Hi I am working on a worksheet that has a combo box I would like that when the user selects a value from combo box once the value is selected the event goes on to create the lists for the other comboboxes (by copying the values from a sheet and sort)..... Here is the code I have so far..... my problem is when I run the other routines that have nothing to do with the combo box the comboBox event somehow is being triggered.... please help...


Private Sub ComboBox1_Click()

Dim NumCount As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False 'turn displays/warning off
Application.EnableEvents = False 'turn off events so that the work below doesn't trigger them

Worksheets("Dealer").ComboBox1.ListFillRange = "DealerList"
Worksheets("CalPage").Range("D4").Value = Worksheets("Dealer").ComboBox1.Value
'If KeyCode = 13 Then '13 = Enter / Return
'If Worksheets("Dealer").ComboBox1.Activate Then

If Worksheets("CalPage").Range("B16").Value = 0 Then

Worksheets("Dealer").ComboBox2.Enabled = False
Worksheets("Dealer").ComboBox3.Enabled = False
Worksheets("Dealer").ComboBox4.Enabled = False
Worksheets("Dealer").CommandButton1.Enabled = False

MsgBox "Dealer Not Found", vbOKOnly
Exit Sub

Else

Application.ScreenUpdating = False
Application.DisplayAlerts = False 'turn displays/warning off
Application.EnableEvents = False 'turn off events so that the work below doesn't trigger them

'Create Survey Type List

Worksheets("Lists").Range("B3:B4").Value = Worksheets("CalPage").Range("O17:O18").Value

Worksheets("Lists").Select
Worksheets("Lists").Range("B3:B4").Select

Worksheets("Lists").Sort.SortFields.Clear
Worksheets("Lists").Sort.SortFields.Add Key:=Range("B3:B4"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Worksheets("Lists").Sort
.SetRange Range("B3:B4")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Worksheets("Lists").Range("A2:B2").Select

'Create Region List
Worksheets("Lists").Range("D3:D7").Value = Worksheets("CalPage").Range("U17:U21").Value

Worksheets("Lists").Select
Worksheets("Lists").Range("D3:D7").Select

Worksheets("Lists").Sort.SortFields.Clear
Worksheets("Lists").Sort.SortFields.Add Key:=Range("D3:D7"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Worksheets("Lists").Sort
.SetRange Range("D3:D7")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Worksheets("Lists").Range("C2:D2").Select

'Create Brand List
Worksheets("Lists").Range("I3:I6").Value = Worksheets("CalPage").Range("R17:R20").Value

Worksheets("Lists").Select
Worksheets("Lists").Range("I3:I6").Select

Worksheets("Lists").Sort.SortFields.Clear
Worksheets("Lists").Sort.SortFields.Add Key:=Range("I3:I6"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Worksheets("Lists").Sort
.SetRange Range("I3:I6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Worksheets("Lists").Range("H2:I2").Select

Worksheets("Dealer").Select

Worksheets("Dealer").ComboBox2.ListFillRange = "SurveyTypeList"
Worksheets("Dealer").ComboBox3.ListFillRange = "RegionList"
Worksheets("Dealer").ComboBox4.ListFillRange = "BrandList"
Worksheets("Dealer").ComboBox2.Enabled = True
Worksheets("Dealer").ComboBox3.Enabled = True
Worksheets("Dealer").ComboBox4.Enabled = True
Worksheets("Dealer").CommandButton1.Enabled = True

End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True 'turn displays/warning off
Application.EnableEvents = True

End Sub

 

[AlphaFrog]

Do the other procedures change the ComboBox1's list fill range?

It may help to deselect the item from combobox1 at the end of its click procedure.
Me.ComboBox1.ListIndex = -1

 

[rudevincy]

no the other procedures do not change the ComboBox1 list fill range..... the other procedure is a filter one that according to the what the user selected the data is filter by that..... so it will look at the cell the combo box puts the selected value

 

[AlphaFrog]

no the other procedures do not change the ComboBox1 list fill range..... the other procedure is a filter one that according to the what the user selected the data is filter by that..... so it will look at the cell the combo box puts the selected value

Filtering the list fill range is a change that will trigger a click event.

 

[rudevincy]

when the user picks a value from combo box 1 it is assigned to a cell (a1) and excel formulas are used to pull the data that is needed... then based on the data that is pulled the other combo box (2-5) lists are created, then the user will select values from combo box (2-5) that will be assigned to a cell (A2-5)... then they click the create chart button and the data that was pull is pasted to Sheet1 where based on what is in the assigned cells cell (A2-5) the data is filter... so the combo box list fill range is never used anywhere else except to populate combo box 1......

 

[AlphaFrog]

Does the combobox1 listfill range share rows where you filter a different list in another column and that filter affects the combobox1 list rows? Otherwise, I'm out of ideas.

 

[rudevincy]

no they are on two different worksheets