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

Open in new window


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

Open in new window

ASKER CERTIFIED SOLUTION
Shanan212

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
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