Prevent Combo Box Change Event Triggers

[Starnub]

I've followed a guide on how to create a searchable combo box, but the change event triggers every time the workbook is changed. Is there a way to prevent the event from triggering when the combo box is not selected or when the user isn't searching?

 

[Shortmeister1]

You have got the application.events = false statement, which is used to stop infinite loops within a sub, but that probably won't help here.

You might be better off accepting that it's going to fire up and use some conditional logic to do/not do anything.

eg. if the combobox is changed (change event) or gotfocus event then do something, else do nothing.

 

[Starnub]

Dang, fair enough. I'll just leave it as is then. Thanks for the reply!

 

[Brian R Armstrong]

Is it possible provide an example how to solve this.

I attach my code for information, any help would be appreciated

Private Sub ComboBox1_Change() 
    Dim result As Range, x As Integer, CtrlSrc As String
    Dim c14 As Integer, c13 As Integer
    On Error Resume Next
   Set result = SearchRange.Find(ComboBox1.Text, , xlValues, xlWhole)
    If result <> "Name" Then
        CtrlSrc = result.Parent.Name & "!" & result.Offset(, x).Address
        c13 = result.Offset(0, 13).Value 
        c14 = result.Offset(0, 14).Value 
        result.Offset(0, 12).Value = tb_12
        result.Offset(0, 13).Value = tb_12 + c13 
        result.Offset(0, 14).Value = c14 + tb_12
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim R As Range
    Set SearchRange = ThisWorkbook.Names("SearchRange").RefersToRange
    For Each R In SearchRange
        ComboBox1.AddItem R.Text
    Next
End Sub

Many Thanks Brian A

 

[RoryA]

You can use a flag variable that works (a bit) like Enableevents does for Excel's events:

Dim bSkipEvents as Boolean ' this goes before all routines in the userform
Private Sub ComboBox1_Change()
If bSkipEvents then exit sub 
    Dim result As Range, x As Integer, CtrlSrc As String
    Dim c14 As Integer, c13 As Integer
    On Error Resume Next
   Set result = SearchRange.Find(ComboBox1.Text, , xlValues, xlWhole)
    If result <> "Name" Then
        CtrlSrc = result.Parent.Name & "!" & result.Offset(, x).Address
        c13 = result.Offset(0, 13).Value 
        c14 = result.Offset(0, 14).Value 
        result.Offset(0, 12).Value = tb_12
        result.Offset(0, 13).Value = tb_12 + c13 
        result.Offset(0, 14).Value = c14 + tb_12
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim R As Range
    Set SearchRange = ThisWorkbook.Names("SearchRange").RefersToRange
    bSkipEvents = True
    For Each R In SearchRange
        ComboBox1.AddItem R.Text
    Next
bSkipEvents = False
End Sub

Note that unless you specifically need the formatted values of the cells, you can simply assign the whole range's Value property to the List property of the combobox without needing to loop.

 

[Brian R Armstrong]

Hi Rory, many thanks for your reply, I have tried your code but no change, i.e. when searching combobox, for example j, jo brings up Jean, John, the textbox value is added to Jean and John in the spread sheet. Presently there all 300 names to search.

I hope this make sense
Brian A

 

[RoryA]

OK, that wasn't clear from what you posted. If you don't want that, the simplest thing is to just not use the Change event and use say a separate search button.

 

[Brian R Armstrong]

Thanks Rory, I will lookup some input box codes

Kind Regards Brian A