Combo Box Trigger

rudevincy

Active Member
Joined
Feb 21, 2005
Messages
415
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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

Active Member
Joined
Feb 21, 2005
Messages
415
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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

Active Member
Joined
Feb 21, 2005
Messages
415

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top