How to unable Worksheet_Change from Excel VBA if Macro is enabled

Question

well I have a big doubt, actually I'm developing a excel vba application, but I have a problem, I'm using the event of worksheet (Worksheet_Change), but I want that this event doesn't execute if macro is running...

The macro runs or enable when the user press a button, so I want that if the user click the button (active the macro) all the logic or functions that I have in Worksheet_Change can not be executed..

I have this in my worksheet_change:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range, Rng As Range
    Dim SearchString As String
    Dim myValue As Variant
    Set Rng = Range("F10:F153")
    Dim myList As Object
    Set myList = CreateObject("Scripting.Dictionary")
    myList.Add "1234", 1
    myList.Add "12345", 2
    myList.Add "123456", 3
    SearchString = "Error"

    For Each aCell In Rng
        If InStr(1, aCell.Value, SearchString, vbTextCompare) Then
            MsgBox "Error encontrado. "
            myValue = InputBox("Inserte el numero de empleado de algun operador de calidad")
            If myList.Exists(myValue) Then
                MsgBox "Numero de empleado correcto, verifique su error."
            Else
            While myList.Exists(myValue) = False

                myValue = InputBox("Inserte el numero de empleado de algun miembro de calidad")
            Wend
            MsgBox "Numero de empleado correcto, verifique su error."
            End If
        End If
        Next
End Sub

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2017-04-26T15:07:12

Here's a minimal example of turning the event on or off depending on some button a user can press.

You don't mention in your question what button is pressed - or what code is run - but you should be able to adapt the code below to your circumstances:

You have two buttons on a sheet:

In in the Sheet1 code module:

Option Explicit

Dim blnUserFlag As Boolean

Public Sub Button1_Click()
    blnUserFlag = True
End Sub

Public Sub Button2_Click()
    blnUserFlag = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If blnUserFlag = False Then
        'do stuff
        Debug.Print "change"
    End If
End Sub

Right click each button and use the Assign Macro option to assign Button1_Click and Button2_Click to the buttons.

The technique is to have a flag (a Boolean) variable - blnUserFlag - outside of the event code that can be turned 'on' or 'off' by the buttons. This then controls whether the code inside the event can run.

How to unable Worksheet_Change from Excel VBA if Macro is enabled

2 Answers