Prevent private sub macro from running

**jman0707** · 11-14-2008, 10:29 PM

I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton. Thanks!

**BigBas** · 11-14-2008, 10:41 PM

One option is to have the command button add text to a random cell when clicked (for example, add an "x" to cell Z1. Then you can add a test to the Worksheet_Change Event.

A quick example:

If Range("Z1") <> "x" then
  'Run the code
End If

**jman0707** · 11-14-2008, 10:59 PM

Thanks! 2 quick questions. i've never had a button put text in a cell, what syntax does that use? And once I push the button, won't the text stay in the cell and then every time I change something in the worksheet it won't run the worksheet_change event? Or does it somehow test it, then I can get it to delete?

**JBeaucaire** · 11-14-2008, 11:37 PM

Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
    If CommandButton1.Caption = "Macro ON" Then
        CommandButton1.Caption = "Macro OFF"
        CommandButton1.ForeColor = &HFFFF&
        CommandButton1.BackColor = &H808080
        Range("AA1").Value = ""
    Else: CommandButton1.Caption = "Macro ON"
        CommandButton1.ForeColor = &HFFFFFF
        CommandButton1.BackColor = &H800000
        Range("AA1").Value = 1
    End If
End Sub

Then BigBas' code adjusts to watch AA1 as so:

If Range("AA1") = 1 then
  'Run the code
End If

**jman0707** · 11-15-2008, 01:04 AM

It can't be an off/on sort of thing, I need it the commandbutton to clear certain rows without triggering worksheet_change, then allow the worksheet_change event to be immediately active again. Here's what I put in the commandbutton code:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Const pw As String = "password"
Dim ce As Variant
ActiveSheet.Unprotect pw
    Range("AA1").Value = "x"
    For Each ce In Sheets("CONTROL").Range("S129:S228")
    If ce.Value = 1 Then ActiveSheet.Cells(ce.Row - 112, "B").Resize(1, 13).ClearContents
    Next ce
 Application.ScreenUpdating = True
 ActiveSheet.Protect pw, AllowSorting:=True, AllowFiltering:=True
End Sub

And this in the workshee_change event right in the beginning:

If Range("AA1").Value = "x" Then
    Application.ScreenUpdating = False
    Range("AA1").Value = ""
    Application.ScreenUpdating = True
    Exit Sub
End If

But it's still not quite working. Any ideas?

**JBeaucaire** · 11-15-2008, 01:31 AM

You haven't told us what you're deleting, so you'll have to edit the "clear" event in this code to delete what you want, but this commandbutton macro will work:

Private Sub CommandButton1_Click()
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Range("C1:E10").Clear
        Application.EnableEvents = True
End Sub