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]

How about for an ActiveX CheckBox named CheckBox1, add this line at the location shown, to your code.

'declare global variable
 Dim oldValue As String
 Dim oldAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If CheckBox1 = True Then
    Exit Sub
End If

'declare variables for individual sheets

 

[lv2424]

How about for an ActiveX CheckBox named CheckBox1, add this line at the location shown, to your code.

'declare global variable
 Dim oldValue As String
 Dim oldAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If CheckBox1 = True Then
    Exit Sub
End If

'declare variables for individual sheets

wouldn't I need to put something in the 'Sub CheckBox1_Click() sub?

 

[igold]

No. Did you try it.

 

[lv2424]

No. Did you try it.

yes and when i click the check box the sub still runs

 

[igold]

Did you use an ActiveX Checkbox named Checkbox1 and did you place the checkbox on the same worksheet that your that your Workbook_SheetChange code is acting upon.

This tested and worked correctly for me using your code with my addition as shown. Once that box is checked your code will exit at that line and not complete.

 

[lv2424]

yea still nothing :/ I used an activex checkbox named checkbox1 and put it on the change log sheet with no change. then put it on the sheets im making changes to and still nothing.

Did you use an ActiveX Checkbox named Checkbox1 and did you place the checkbox on the same worksheet that your that your Workbook_SheetChange code is acting upon.

This tested and worked correctly for me using your code with my addition as shown. Once that box is checked your code will exit at that line and not comp

 

[igold]

I don't know what to tell you. Did you try putting a stop on the start of the code so that you could then walk through the code and watch what is happening. If your code is running it has to get through the If/Then that I added. What does the CheckBox1 value show...

 

[lv2424]

I don't know what to tell you. Did you try putting a stop on the start of the code so that you could then walk through the code and watch what is happening. If your code is running it has to get through the If/Then that I added. What does the CheckBox1 value show...

I just added if the check box is clicked then this random cell = 1; if not random cell = 2 then put if random cell = 1 exit sub and it works. hide random cell with the check box itself

 

[igold]

Can you post that portion of the code.. Also, did your try stepping through the code to see what is happening...