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