How To Stop ComboBox1_Click Triggering Worksheet_Change Repeatedly And Messing Up Target Address?

Question

I have a "form filling" worksheet with VBA Macro to automatically move the data to another "database" worksheet. Then, there is ComboBox to search and retrieve data from database.

The mechanism is ComboBox_Click will copy it text value to Target.Address which will trigger Worksheet_Change and finally the data will retrieved and displayed.

The problem is the ComboBox_Click triggering Worksheet_Change several times (about 3 times) ~~and messing up the specified Target.Address. If I edit cell value outside the specified Target.Adress the Worksheet_Change will triggered, every cell seem acting as Target.Address now.~~ EDIT: Target Address IS NOT messed up

How can I stop this?

This is the VBA code in "form-filling" worksheet, simplified, not my code, I edited it from the code I got from a Website.

Option Explicit
----------------------------------------------------
Private Sub ComboBox1_Click()

    Me.Range("myTargetAddress").Value = Me.Range("myComboBoxValue").Value
    'myComboBoxValue is ComboBox LinkedCell property

End Sub
----------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wForm as Worksheet
    Dim wDatabase as Worksheet
    Dim lRowNextEmpty As Long
    Dim lRowLastFilled As Long
    Dim lRowMyDataPosition As Long
    Dim lRowMyDataPostionExact As Long
    Dim rMyDataToFill As Range

    Set rMyDataToFill = wForm.Cells("C3:C12")

    ... 'collapsed to concise

    Application.EnableEvents = False

    Select Case Target.Address
        Case Me.Range("myTargetAddress").Address

        Case Else
        GoTo WaitAndSee
    End Select

    With wDatabase
        lRowNextEmpty = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row - 1
        lRowLastFilled = lRowNextEmpty - 1
    End With

    With wDatabase
        lRowMyDataPostion = wForm.Range("A1").Value 'A1 contain formula to match lookup
        If  lRowMyDataPostition > 0 And lRowMyDataPosition <= lRowLastFilled
            lRowMyDataPositionExact = lRowMyDataPosition + 1 '+ 1 to overcome column header
            .Range(.Cells(.lRowMyDataPositionExact, 1), .Cells(lRowMyDataPositionExact, 10).copy
            'this will copy, for excample, A1:J1 from database 'J is the 10th column
            rMyDataToFill.Cells(1,1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        End If
    End With


WaitAndSee:

    Application.EnableEvents = True
    Exit Sub

End Sub

inside the Worksheet_Change sub you could add something like if not Intersect(Target, myTargetAddress) is nothing then to narrow down the range that is affected by the change. — horst
Thanks. I am not a coder my self, just modifying other's code. I can't code from scratch. From several test, I noticed it wasmyComboBoxValue LinkedCell that cause the trouble, so I need to clear it. Then, I just need to add If Me.Range("myComboBoxValue") = "" Then Application.EnableEvents = True Exit Sub in sub ComboBox1_Click(). Not perfect, because although the data is retrieved, the ComboBox gone empty. Oh, I mistakenly guessed the specified Target Address messed up, in fact it IS NOT. I know it because I add MsgBox everywhere to find the bug, sorry my bad. — ariefcfa

FFFffff FFFffff · Accepted Answer · 2019-03-08T14:41:40

If you do not want to trigger Worksheet_Change() inside ComboBox1_Click(), you can disable application events via Application.EnableEvents (make sure to enable it later on). If you want to trigger the code, but only once, you can disable events and call the procedure explicitly:

Private Sub ComboBox1_Click()
    Application.EnableEvents = False

        Me.Range("myTargetAddress").Value = Me.Range("myComboBoxValue").Value
        'myComboBoxValue is ComboBox LinkedCell property

        '''  if you want to call Worksheet_Change() (once), uncomment the next line
        ' Call Worksheet_Change(Target:=Me.Range("myTargetAddress"))

    Application.EnableEvents = True
End Sub

How To Stop ComboBox1_Click Triggering Worksheet_Change Repeatedly And Messing Up Target Address?

1 Answers