Workbook opening and closing shows Enable Macro screen

MRK_Toledo

New Member
Joined
Sep 2, 2006
Messages
34
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.....

Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

MRK_Toledo

New Member
Joined
Sep 2, 2006
Messages
34
I would like to eliminate the Macros Disabled screen from being seen during the delay in opening and closing the workbook.
 
L

Legacy 98055

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

Forum statistics

Threads
1,137,300
Messages
5,680,699
Members
419,928
Latest member
dolincasting

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 MrExcel.com.
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 "mrexcel.com".
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
Top