How can I disable the change log sub that I have written by checking a check box?

[lv2424]

I have written the below code in excel vba to log changes made in sheets to the change log sheet. I want to disable that sub if a check box is checked.

This code works perfectly for what I need it to do, just need to figure out a sub that does not allow this to run if a box is checked so every change is not being logged when necessary.

'declare global variable
 Dim oldValue As String
 Dim oldAddress As String
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'declare variables for individual sheets
Dim sheet1 As String
NSWCCDFY22M = "Sheet 1"

Dim sheet2 As String
NSWCCDFY23M = "Sheet 2"

Dim sheet3As String
NSWCCDLSW = "Sheet 3"

'Logs change for any sheet that isnt the log itself (address, values, user, date/time, hyperlink, note)
If ActiveSheet.Name <> "ChangeLog" Then
    Application.EnableEvents = False
    Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
    Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
    Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
    Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
    Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
   
    'hyperlink to specific sheet
    If ActiveSheet.Name = Sheet 1 Then
        Sheets("ChangeLog").Hyperlinks.Add Anchor:=Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & Sheet 1 & "'!" & oldAddress, TextToDisplay:=oldAddress
    ElseIf ActiveSheet.Name = Sheet 2 Then
        Sheets("ChangeLog").Hyperlinks.Add Anchor:=Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & Sheet 2 & "'!" & oldAddress, TextToDisplay:=oldAddress
    ElseIf ActiveSheet.Name = Sheet 3 Then
        Sheets("ChangeLog").Hyperlinks.Add Anchor:=Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & Sheet 3 & "'!" & oldAddress, TextToDisplay:=oldAddress
  
    End If

    'input box for note
    Dim commentChange As String
    commentChange = InputBox("Please enter a note for this change.", "Logging")
    Sheets("ChangeLog").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = commentChange
   
    'if input box is not filled in
    If LenB(commentChange) = 0 Then
        MsgBox "You must enter a note for the change you've just made." & vbCrLf & " " & vbCrLf & "You will be taken to the Change Log to add a note and can navigate back to this sheet using the link associated with your change.", vbExclamation, "Change Log Required Actions"
        Sheets("ChangeLog").Select
       
        'go to log if a note is not put in
        Dim lRow As Long
        Dim lColumn As Long
        lRow = Range("A1").End(xlDown).Row
        lColumn = Range("A1").End(xlToRight).Column
        Cells(lRow, lColumn).Select
        Dim OutPut As Integer
        'infobox when taken to log
        OutPut = MsgBox("1. Please enter a note for the change you've just made." & vbCrLf & " " & vbCrLf & "2. Click the link in the 'Link' Column to return to the previous sheet where the change was made.", vbInformation, "Change Log Required Actions")
End If

Sheets("ChangeLog").Columns("A:G").AutoFit
Application.EnableEvents = True

End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Count = 1 Then
oldValue = Target.Value
End If
oldAddress = Target.Address
End Sub

 

[igold]

Are we both talking about the same thing. You wrote

I just added if the check box is clicked then this random cell = 1

You click CommandButtons and you Check Checkboxes. Stranger things have happened...