BeforeSave and BeforeClose Macro Save As cannot choose file path?

[marturab]

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? If anyone could help me here I would appreciate it greatly-I'm literally on the verge of getting fired if I can't figure this out soon.

 

[JoeMo]

You need to specify the file format in the SaveAs command line. Try this:

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
ActiveWorkbook.SaveAs varWorkbookName, FileFormat:=52
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

 

[marturab]

Thanks so much for replying man, I sincerely appreciate it. I tried that code but the workbook still is not allowing me to save anywhere other than the file path it was originally in. For instance, after the Save As dialog box comes up and I specify to save to my desktop, it just overwrites the original file in the folder location that I opened it from, not saving a new copy with the new file name. It's super weird, since any changes are being saved, but it just overwrites the original file in that location with the original file name, instead of letting me specify a name and location and creating another file. Any thoughts? Again, thank you so much for the help, you are a lifesaver right now.

 

[JoeMo]

Thanks so much for replying man, I sincerely appreciate it. I tried that code but the workbook still is not allowing me to save anywhere other than the file path it was originally in. For instance, after the Save As dialog box comes up and I specify to save to my desktop, it just overwrites the original file in the folder location that I opened it from, not saving a new copy with the new file name. It's super weird, since any changes are being saved, but it just overwrites the original file in that location with the original file name, instead of letting me specify a name and location and creating another file. Any thoughts? Again, thank you so much for the help, you are a lifesaver right now.

I can't reproduce the behavior you describe. The code I posted works perfectly for me when I do a SaveAs and change the path to one that differs from the path the workbook was originally saved to.

 

[marturab]

Ah, so I think I know why we are getting different results. I set my workbook up to hide all headings and everything for the end user. The only way they can close the file is by clicking the X in the top right corner. That is what then kicks off the BeforeClose event, which opens the SaveAs dialog box. It is from that dialog box that I am unable to specify the name and location of the folder. I have a feeling the issue is that the BeforeClose and BeforeSave events aren't playing well together.

 

[JoeMo]

Ah, so I think I know why we are getting different results. I set my workbook up to hide all headings and everything for the end user. The only way they can close the file is by clicking the X in the top right corner. That is what then kicks off the BeforeClose event, which opens the SaveAs dialog box. It is from that dialog box that I am unable to specify the name and location of the folder. I have a feeling the issue is that the BeforeClose and BeforeSave events aren't playing well together.

Sounds like a reasonable feeling. I would stick with the before save event macro for SaveAs events.