Combobox change event is firing for any changes in worksheet-CodePudding

I tried to have ComboBox in my spreadsheet but it does not work the way I want. The problem I have is that the combobox event (drop down) is triggered for any changes made in the work sheet. for example, I have combobox linked to cell A1, whenever I change the value in H9 cell, (or any other cells) the dropdown of the combobox is triggered. I want to trigger the combobox drop box only when its linked cell changes, which is A1.

Private Sub ComboBox1_Change()
'DROP-DOWN USE CATEGORY BOX ///////////////////////////////////////////////////////
Dim Use As String
Dim Ind As String
Use = Worksheets("PEC Calculator").Range("B8").Value
Ind = Worksheets("PEC Calculator").Range("B3").Value
If ComboBox1.Value <> "" Then
ComboBox1.ListFillRange = "UC_List"
Me.ComboBox1.DropDown
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Dim tblA As ListObject
Dim nRows As Long
Dim nCols As Long

Set tblA = Worksheets("PEC Calculator").ListObjects("ATableINPUT")

If tblA.Range(2, 2).Value = "TableA1" Then
    If Range("B4").Value = "Batch" Then
    tblA.Range(3, 2) = 0.000001
    Else
    tblA.Range(3, 2) = 0.000001
    End If
End if
Application.EnableEvents = True
End Sub

Is there any way to fix this issue? any idea?

CodePudding user response：

The ComboBox.Change event triggers everytime the .Value property changes. The .LinkedCell property that you have set to A1 is causing these events to fire on every sheet change because A1 contains a formula.

Some formulas in Excel ask to be recalculated on every sheet change.
Even if the new value is the same as the old value, it still triggers the ComboBox.Change event.

To resolve this issue, I suggest declaring a Module-Level variable and using it to save the ComboBox's Value. Module-Level Variables retain their value between executions. This way you can compare the old and new value during each event and only execute your actions when the value has changed.

Dim CB_Val As Variant
Private Sub ComboBox1_Change()
    If Me.ComboBox1.Value <> CB_Val Then
        CB_Val = Me.ComboBox1.Value
        'DROP-DOWN USE CATEGORY BOX ///////////////
        Dim Use As String
        Dim Ind As String
        Use = Worksheets("PEC Calculator").Range("B8").Value
        Ind = Worksheets("PEC Calculator").Range("B3").Value
        If ComboBox1.Value <> "" Then
            ComboBox1.ListFillRange = "UC_List"
            Me.ComboBox1.DropDown
        End If
    End If
End Sub