Workbook opening and closing shows Enable Macro screen


New Member
Sep 2, 2006
I have a workbook that I have made to check to see if Macros are
enabled. If they are, then the workbook will open.

If macros are not enabled - then it displays a Macros Disabled page that tells the user they must Enable Macros to use this workbook.

The problem is this: When the user selects the Enable Macros button
while opening the workbook - the Macros Disabled page displayed for a quick second - and while closing, it displays for about 1 whole second.

Is there a way to eliminate this? I don't want to see the Macros Disabled
page when I open IF the user has selected to enable macros.

Also, I don't want to have that page displayed IF the macros were enabled
when it was opened.

Here is what my code looks like right now.....

Private Sub Workbook_Open()

'With ActiveWindow
 '       .DisplayWorkbookTabs = False
  '      .DisplayHeadings = False
   '     .DisplayHorizontalScrollBar = False
    'End With

'With Application
 '       .CommandBars("Worksheet Menu Bar").Enabled = False
  '      .CommandBars("Standard").Visible = False
   '     .CommandBars("Formatting").Visible = False
    '    .DisplayFormulaBar = False
     '   .DisplayStatusBar = False
    'End With


End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim i As Long
    Dim x As Long
    Dim ShArray()
    Dim ShName As String
    Dim ShtsNotToPrint As Variant
    Dim msg As String
    Dim counter As Integer
    For i = 1 To ActiveWindow.SelectedSheets.Count
    ShName = ActiveWindow.SelectedSheets(i).Name
        If Not IsError(Application.Match(ShName, ShtsNotToPrint, 0)) Then
        msg = msg & vbLf & ShName
        counter = counter + 1
        ReDim Preserve ShArray(x)
        ShArray(x) = ShName
        x = x + 1
        End If
    Next i
    On Error Resume Next
    i = UBound(ShArray)
    If Err Then
    Cancel = True
    MsgBox "RESTRICTED: This page will not Print" & IIf(counter > 1, "s", "") & ":" & _
    msg, vbOKOnly + vbCritical, "ILLEGAL FUNCTION"
        If msg <> vbNullString Then
        MsgBox "You are not allowed to print the following sheet" & IIf(counter > 1, "s", "") & ":" & _
        msg & vbCrLf & "Permitted selections will be printed !", vbOKOnly + vbCritical, "Print Error"
        End If
    End If
    On Error GoTo 0

End Sub
Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True


End Sub

Private Sub UnhideSheets()
Dim sht As Object

Application.ScreenUpdating = False

For Each sht In ThisWorkbook.Sheets

sht.Visible = xlSheetVisible

Next sht

ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden

Application.ScreenUpdating = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Mypath = "C:\Junk\"
MyFileName = Sheets("start").Range("k13").Text
MyFileName2 = Sheets("start").Range("d21").Text
MyFileName3 = Sheets("start").Range("d25").Text
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=Mypath & MyFileName & MyFileName2 & MyFileName3 & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ThisWorkbook.Saved = True

'With ActiveWindow
 '      .DisplayWorkbookTabs = True
  '     .DisplayHeadings = True
   '    .DisplayHorizontalScrollBar = True
   'End With

'With Application
 '      .CommandBars("Worksheet Menu Bar").Enabled = True
  '     .CommandBars("Standard").Visible = True
   '    .CommandBars("Formatting").Visible = True
    '   .DisplayFormulaBar = True
     '  .DisplayStatusBar = True
    'End With
End Sub

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I would like to eliminate the Macros Disabled screen from being seen during the delay in opening and closing the workbook.
Upvote 0
Hi Mark? Please email your workbook. There's just too much legwork involved in trying to duplicate your scenario. See the link below. Also, you may want to consider another method. When I have one or more workbooks that MUST have macros enabled in order to function properly and it just does not fit to create an addin, I use a launcher. I use standalone Visual Basic but i see no reason why you could not create it within Excel. Anyway, something to think about...
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back