I'm trying to create a workbook that has one sheet open when macros are not enabled, and another when they are. I thought I had it working very well, until I tried it when another workbook is open at the same time. When there are more than one workbook open and I try to close without saving my file, it crashes Excel. This only crashes when I close without saving my changes. Can anyone offer a suggestion on what is going on and how to fix it. Here is all my code that resides in the 'ThisWorkbook' object:
'----------------------CODE STARTS HERE------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
'Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Hide all sheets
Call HideAllSheets
ThisWorkbook.Save
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
'Application.EnableEvents = True
.Saved = True
.Close savechanges:=False
End If
End With
'Application.EnableEvents = True
End Sub
'*******************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim varWorkbookName As String
Dim FileFormatValue As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
'Forces save as .XLSM file type.
Call HideAllSheets
If SaveAsUI = True Then
varWorkbookName = Application.GetSaveAsFilename( _
fileFilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm")
Cancel = True
If varWorkbookName <> "False" Then
Select Case LCase(Right(varWorkbookName, Len(varWorkbookName) - InStrRev(varWorkbookName, ".", , 1)))
Case "xlsm": FileFormatValue = 52
End Select
ActiveWorkbook.SaveAs varWorkbookName
End If
Else
ThisWorkbook.Save
End If
Call ShowAllSheets
Sheet1.Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
'*******************
Private Sub Workbook_Open()
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMaximized
Sheet1.Visible = True 'Form sheet
Sheet1.Activate
Sheet3.Visible = xlSheetVeryHidden 'No Macros Sheet
Sheet2.Visible = xlSheetHidden 'Defaults
Sheet4.Visible = xlSheetHidden 'Data_Fields
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'*******************
Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Sheet3.Visible = xlSheetVisible 'Macros Not Enabled
Sheet1.Visible = xlSheetVeryHidden 'Item Upload
Sheet2.Visible = xlSheetVeryHidden 'Defaults
Sheet4.Visible = xlSheetVeryHidden 'Data Fields
Sheet3.Activate
'-----------------------CODE ENDS-------------------------------------
Sheet 1 is the sheet that opens when Macros are enabled. Sheet 3 is the sheet that opens when they are not.
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar