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