Suppress Worksheet_Activate When Sheet Activated From Other Macro

bdr0121 · Feb 25th 2021

Hi. I have a userform that lets the user select an open workbook and make a few updates to the specific workbook's tabs. Some of the workbook tabs have a worksheet_activate event set and sometimes that event will run into an error (usually an unrelated subscript out of range error). I'm hoping there's a way to suppress the external book's worksheet_activate. I've tried turning enableevents off, but that doesn't seem to work as the error will still display. I also included a on error resume next in the userform's procedure, but that doesn't stop it either.

If it helps, below is the userform code where the external workbooks sheets are looped through for certain updates.

Code

Private Sub cboProcess_Click()

Dim wb As Workbook
Set wb = Workbooks(Me.cboOpenWBs.Value)
Dim sh As Worksheet

wb.Application.EnableEvents = False

Dim password As String
password = "admin"

If Me.chbCells.Value = True Then
  
    If WorksheetExists2("API Extract") Then
    
        For Each sh In wb.Sheets
   
        If sh.Name = "results" Or sh.Name = "API Extract" Or sh.Name = "Auditor Setup" Or sh.Name = "API Write" Then
        Else
            With sh
                .Unprotect password
                .Cells.Locked = False
                .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
                .Protect password
            End With
        End If
        Next sh
    Else
    
    For Each sh In wb.Sheets
        With sh
            .Unprotect password
            .Cells.Locked = False
            .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
            .Protect password
        End With
    Next sh
    End If

End If

If Me.chbFooter.Value = True Then

Dim ctr As String, pub As String
ctr = Me.txtDocCtrl
pub = Me.txtPubDt

    If WorksheetExists2("API Extract") Then
    
        For Each sh In wb.Sheets
   
        If sh.Name = "results" Or sh.Name <> "API Extract" Or sh.Name <> "Auditor Setup" Or sh.Name <> "API Write" Then
        Else
            With sh
            .Unprotect password
            .PageSetup.RightFooter = ctr & Chr(10) & pub
            .Protect password
            End With
        End If
        Next sh
    Else
    
    For Each sh In wb.Sheets
        With sh
            .Unprotect password
            .PageSetup.RightFooter = ctr & Chr(10) & pub
            .Protect password
        End With
    Next sh
    End If

End If

wb.Application.EnableEvents = True

Dim foot As Variant
Dim upd As String
foot = wb.Sheets("Main").PageSetup.RightFooter
upd = Right(foot, Len(foot) - Application.WorksheetFunction.Find(Chr(10), foot))

If Me.chbCells.Value = True And Me.chbFooter.Value = True Then
    MsgBox "Processing completed for the following... " & _
        vbCrLf & "1) Formula cells have been protected on all sheets" & _
        vbCrLf & "2) Document " & ctr & " has been updated with a " & _
        "published date of " & upd, vbInformation, "Completed"
ElseIf Me.chbCells.Value = True And Me.chbFooter.Value = False Then
    MsgBox "Processing completed for the following... " & _
        vbCrLf & "1) Formula cells have been protected on all sheets" & _
        vbInformation, "Completed"
ElseIf Me.chbCells.Value = False And Me.chbFooter.Value = True Then
    MsgBox "Processing completed for the following... " & _
        vbCrLf & "1) Document " & ctr & " has been updated with a " & _
        "published date of " & upd, vbInformation, "Completed"
End If

Unload Me
End

End Sub

Display More

**norie** · Feb 25th 2021

Are you opening the other workbooks in another instance of Excel?

bdr0121 · Feb 25th 2021

Sorry, I should have specified that part. The userform will load all open workbooks in a combobox. The selected workbook becomes the wb variable in the posted code. So, no. The other workbook is already open. The idea is that many individual workbooks would be open and updated specific to each workbook could be made. Should it maybe be closed and I should have the user pick the file with the file picker to avoid the worksheet activate?

**rory** · Feb 25th 2021

Your code doesn't appear to activate any sheets, and the enableevents lines will prevent any application events from firing, so I think the problem must be elsewhere. What's the code for WorksheetExists2?

Also, just FYI:

1. there is no need to use WorksheetFunction.Find as VBA has the InStr function.

2. This line is pointless, since it will always be true:

Code

If sh.Name = "results" Or sh.Name <> "API Extract" Or sh.Name <> "Auditor Setup" Or sh.Name <> "API Write" Then

which means that all the code from lines 16 to 27 serves no purpose.

bdr0121 · Feb 25th 2021

You were spot on with those lines not working. The intent was to bypass those sheets if they were present in the workbook. So I used the If WorksheetExists2 to see if one of those 4 were found. If so, the thought, was that it would bypass them. But, since that didn't work, I did some rearranging to remove that part and instead use if WorksheetExists2 to simply unprotect those sheets after it updates the sheet's cell protection and/or the sheet's footer. As long as those sheets aren't protected, then it should bypass further trouble there.

So, thank you very much for that. But, back to the worksheet_activate issue, I 100% agree with you that nothing is deliberately activating these sheets. But, I think the code is still moving through those sheets to make the cell protections and footer updates, which is triggering those sheets' own code (if they have them).

Below is the updated code, which still seems to trigger those external book's worksheet events. Thanks for your time!

Code

Private Sub cboProcess_Click()

Dim wb As Workbook
Set wb = Workbooks(Me.cboOpenWBs.Value)
Dim sh As Worksheet

wb.Application.EnableEvents = False

Dim password As String
password = "admin"

If Me.chbCells.Value = True Then
  
    For Each sh In wb.Sheets
   
        On Error Resume Next 'Added because it errors if a sheet has no formulas
        With sh
            .Unprotect password
            .Cells.Locked = False
            .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
            .Protect password
        End With
        On Error GoTo 0
        Next sh

    If WorksheetExists2("API Extract") Then
        wb.Sheets("results").Unprotect password
        wb.Sheets("API Extract").Unprotect password
        wb.Sheets("API Write").Unprotect password
        wb.Sheets("Auditor Setup").Unprotect password
    End If

End If

If Me.chbFooter.Value = True Then

Dim ctr As String, pub As String
ctr = Me.txtDocCtrl
pub = Me.txtPubDt

    For Each sh In wb.Sheets
        With sh
            .Unprotect password
            .PageSetup.RightFooter = ctr & Chr(10) & pub
            .Protect password
        End With
    Next sh
    
    If WorksheetExists2("API Extract") Then
        wb.Sheets("results").Unprotect password
        wb.Sheets("API Extract").Unprotect password
        wb.Sheets("API Write").Unprotect password
        wb.Sheets("Auditor Setup").Unprotect password
    End If

End If

wb.Application.EnableEvents = True

Dim foot As Variant
Dim upd As String
foot = wb.ActiveSheet.PageSetup.RightFooter
upd = Right(foot, Len(foot) - Application.WorksheetFunction.Find(Chr(10), foot))

If Me.chbCells.Value = True And Me.chbFooter.Value = True Then
    MsgBox "The following updates were made to " & Me.cboOpenWBs.Value & "... " & _
        vbCrLf & vbCrLf & "1) Formula cells have been protected on all MARP sheets" & _
        vbCrLf & "2) Document " & ctr & " has been updated with a " & _
        "published date of " & upd, , "Completed"
ElseIf Me.chbCells.Value = True And Me.chbFooter.Value = False Then
    MsgBox "The following updates were made to " & Me.cboOpenWBs.Value & "... " & _
        vbCrLf & vbCrLf & "1) Formula cells have been protected on all MARP sheets" & _
        "", vbInformation, "Completed"
ElseIf Me.chbCells.Value = False And Me.chbFooter.Value = True Then
    MsgBox "The following updates were made to " & Me.cboOpenWBs.Value & "... " & _
        vbCrLf & vbCrLf & "1) Document " & ctr & " has been updated with a " & _
        "published date of " & upd, vbInformation, "Completed"
End If

Unload Me
End

End Sub

Display More

**royUK** · Feb 25th 2021

I would use the On Error for Specialcells like this so that it only covers that error

Code

With sh
 .Unprotect password
 .Cells.Locked = False
 On Error Resume Next 'Added because it errors if a sheet has no formulas
 .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
 On Error GoTo 0
 .Protect password
 End With
 On Error GoTo 0
 Next sh

bdr0121 · Feb 25th 2021

Thanks royUK . That makes sense.

Also, back to the external worksheet error. The error happens after the msgbox information telling the use if the process completed. So, after the user click okay on the msgbox, that's when the external worksheet "activates" for some reason. But, there's literally nothing after the msgbox info except Unload Me and End. So I'm at a loss for why that's happening.

**royUK** · Feb 25th 2021

Sorry, I've accidentally deleted my post. The suggestion was to use error handling like this so that it only works with the Specialcells error that might occur

Code

With sh
.Unprotect password
.Cells.Locked = False
On Error Resume Next 'Added because it errors if a sheet has no formulas
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
On Error GoTo 0
.Protect password
End With
On Error GoTo 0
Next sh

**rory** · Feb 25th 2021

Why are you using End on its own? That is not a good idea generally.

bdr0121 · Feb 25th 2021

Thank you, rory . I have omitted the End as well as moved the wb.application.enableevents = True below the Unload Me. Now, there doesn't seem to be any more issues with the external sheets activating.

Thank you for your help!

Suppress Worksheet_Activate When Sheet Activated From Other Macro

Participate now!

Share

Tags