Hi all, I've searched high and low for the answer to this and spent countless hours trying to figure it out, so I am hoping someone out there can figure out what is going wrong. I am putting the final touches on a big VBA project, and my final sticking point is that I cannot for the life of me figure out how to force a Save As Macro-Enabled Workbook on close. Right now I have a BeforeClose event in the ThisWorkbook module that is as follows:
Application.Dialogs(xlDialogSaveAs).Show
If ThisWorkbook.Saved = "True" Then
Application.Quit
Basically, this code opens a SaveAs dialog box when the user tries to close the workbook. It allows them to save the file wherever they want and name it whatever they want. The reason for the code afterwards is because I have a macro set up to hide all tabs except a "Macro Notification" tab (in order to make sure users enable macros when the workbook is opened) and then auto save and close the workbook so that the user doesn't have to double save.
My issue is that I need the user to only have the option to save as a Macro-Enabled workbook, as I have had an issue with users saving it as a regular Excel file, thereby disabling all macros. I tried using a BeforeSave event, which was so close to working. It allowed the Save As dialog box to pop up when the user closed the workbook, and the only Save As option was a Macro-Enabled workbook. However, it wouldn't let me save the file anywhere else; basically once I hit save, the workbook would save to the original location it was initially opened from, but if I tried to select a different file path it would not save and appear there, just update the file where it was originally. Here is the code I was using that was giving me this problem (again, put in the ThisWorkbook module):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim varWorkbookName As String
Dim FileFormatValue As Integer
On Error Goto Quit
Application.EnableEvents = False
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
End If
Quit:
If Err.Number > 0 Then
If Err.Number <> 1004 Then
MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
"Title", vbCritical
End If
End If
Application.EnableEvents = True
End Sub
Does anyone have any idea why this is doing this? Could it be because the BeforeClose and BeforeSave events aren't playing well together? Is there a way to combine the two into one event to force a Save As dialog box to open on closing and only allow the workbook to be saved as a Macro-enabled workbook? Any help would be awesome; this is driving me totally crazy!!
Application.Dialogs(xlDialogSaveAs).Show
If ThisWorkbook.Saved = "True" Then
Application.Quit
Basically, this code opens a SaveAs dialog box when the user tries to close the workbook. It allows them to save the file wherever they want and name it whatever they want. The reason for the code afterwards is because I have a macro set up to hide all tabs except a "Macro Notification" tab (in order to make sure users enable macros when the workbook is opened) and then auto save and close the workbook so that the user doesn't have to double save.
My issue is that I need the user to only have the option to save as a Macro-Enabled workbook, as I have had an issue with users saving it as a regular Excel file, thereby disabling all macros. I tried using a BeforeSave event, which was so close to working. It allowed the Save As dialog box to pop up when the user closed the workbook, and the only Save As option was a Macro-Enabled workbook. However, it wouldn't let me save the file anywhere else; basically once I hit save, the workbook would save to the original location it was initially opened from, but if I tried to select a different file path it would not save and appear there, just update the file where it was originally. Here is the code I was using that was giving me this problem (again, put in the ThisWorkbook module):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim varWorkbookName As String
Dim FileFormatValue As Integer
On Error Goto Quit
Application.EnableEvents = False
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
End If
Quit:
If Err.Number > 0 Then
If Err.Number <> 1004 Then
MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
"Title", vbCritical
End If
End If
Application.EnableEvents = True
End Sub
Does anyone have any idea why this is doing this? Could it be because the BeforeClose and BeforeSave events aren't playing well together? Is there a way to combine the two into one event to force a Save As dialog box to open on closing and only allow the workbook to be saved as a Macro-enabled workbook? Any help would be awesome; this is driving me totally crazy!!
