troubleshooting Question

One macro is prevent another from running......

Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Excel
8 Comments1 Solution371 ViewsLast Modified:
Hi Experts

i have the following code which works prefectly for what it need to do.....

See Code:

Option Explicit

Private Sub Workbook_Activate()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim MySht As Worksheet

    Set MySht = ActiveSheet
    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .DisplayFormulaBar = False
        .DisplayStatusBar = Not Application.DisplayStatusBar
    End With
    ActiveWindow.DisplayWorkbookTabs = False


    Set wbBook = ThisWorkbook

    For Each wsSheet In wbBook.Worksheets
        With wsSheet
            If Not .Name = "Blank" Then .Activate
            With ActiveWindow
                .DisplayHeadings = False
                .DisplayGridlines = False
            End With
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
        End With
    Next wsSheet

    MySht.Select

End Sub

Private Sub Workbook_Deactivate()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim MySht As Worksheet

    Set MySht = ActiveSheet
    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
    End With
    ActiveWindow.DisplayWorkbookTabs = True


    Set wbBook = ThisWorkbook

    For Each wsSheet In wbBook.Worksheets
        With wsSheet
            If Not .Name = "Blank" Then .Activate
            With ActiveWindow
                .DisplayHeadings = True
                .DisplayGridlines = True
            End With
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
        End With
    Next wsSheet

''RB: I have commented the below code out because (in theory - untested) this prevents the workbook from being deactivated (in fact, even the ".Activate" in the previous section may prevent deactivation.
'    MySht.Select
End Sub

But this code is prevent the following macro from running and openning up worksheet "Source Data"...which i need to work.....also

See Code....
Option Explicit

Sub ToggleVisibilityOfDataWs()
Const MasterName As String = "Master"
Const pw As String = "secret"

    With ThisWorkbook
        If .ActiveSheet.Name = "Master" Then
            With .Worksheets("Source Data")
                If .Visible Then
                    .Visible = False
                Else
                    If InputBox("Please enter password to show Source Data worksheet:", "ENTER PASSWORD...") = pw Then
                        .Visible = True
                    Else
                        MsgBox "Password incorrect!", vbOKOnly + vbInformation, "Macro Feedback..."
                    End If
                End If
            End With
        Else
            MsgBox "Can only run this macro from the worksheet called " & MasterName, vbOKOnly + vbInformation, "Macro Feedback..."
        End If
    End With
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros