CODEWITHSUNDEEP
vbaexcel
bondo455
bondo455

Reputation: 21

Disable then reenable a macro

I am using the following as a timestamp. I would like a button to disable it (so that I can edit without it recording times) and a button to enable it again. 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.column = 12 And Target.Row = 9 Then
    If Target.Value = "" Then
        Target.Offset(0, 4).Value = ""
    Else
        Target.Offset(0, 4).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    End If
ElseIf Target.column = 12 And (Target.Row >= 10 And Target.Row <= 600) Then
    If Target.Value = "" Then
        Target.Offset(-1, 5).Value = ""
    Else
        Target.Offset(-1, 5).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    End If
End If
End Sub

Upvotes: 1

Views: 61

Answers (2)

Animesh
Animesh

Reputation: 228

Use a button which will update cell ZZ200(or as per your choice) with value "ON" or "OFF" and the put your code inside the condition as below :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Sheet1").Range("ZZ200").Value = "ON" Then
        If Target.Column = 12 And Target.Row = 9 Then
            If Target.Value = "" Then
                Target.Offset(0, 4).Value = ""
            Else
                Target.Offset(0, 4).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
            End If
        ElseIf Target.Column = 12 And (Target.Row >= 10 And Target.Row <= 600) Then
            If Target.Value = "" Then
                Target.Offset(-1, 5).Value = ""
            Else
                Target.Offset(-1, 5).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
            End If
        End If
    End If
End Sub

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

use a toggle button, returns true or false, then have the 1st line,if togglebutton then exit sub

Upvotes: 2

Related Questions