Workbook opening and closing shows Enable Macro screen

[MRK_Toledo]

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

UnhideSheets
Sheets("Start").Select
Range("C8").Select
mrksplash.Show


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
    
    ShtsNotToPrint = Array("START", "ENTER MEASUREMENTS", "INVESTMENT CALCULATOR", "ROI")
    
    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
        Else
        ReDim Preserve ShArray(x)
        ShArray(x) = ShName
        x = x + 1
        End If
    Next i
    
    On Error Resume Next
    i = UBound(ShArray)
    If Err Then
    Err.Clear
    Cancel = True
    MsgBox "RESTRICTED: This page will not Print" & IIf(counter > 1, "s", "") & ":" & _
    msg, vbOKOnly + vbCritical, "ILLEGAL FUNCTION"
    Else
    Sheets(ShArray).Select
        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

ThisWorkbook.Save

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
HideSheets
End Sub

 

[MRK_Toledo]

I would like to eliminate the Macros Disabled screen from being seen during the delay in opening and closing the workbook.

 

[Legacy 98055]

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...