Force SaveAs regardless of button pressed - causing crash

mlmaxey · Mar 11th 2020

Good afternoon all,

So I have been searching for a bit now and haven't found anything to help so I've decided to ask the experts.

The code I was using worked fine until our company upgraded to Excel 365 and now all it does is crash. To be exact, I can get the dialog box to open, file name is prefilled, but when I hit save the workbook crashes. However, when I reopen said workbook, I can use the VBA code and it works just fine, it's just that first time saving that is causing problems.

I feel like there must be new code to use or a work around to keep it from hanging. I currently have Version 2002 (12527.20242).

Here's the code: (I pared it down a LOT to hopefully find the snag, but it didn't work)

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strSaveAs As String
strSaveAs = ThisWorkbook.Worksheets("Monthly Payment Calc").Range("E2")
Application.Dialogs(xlDialogSaveAs).Show strSaveAs
End Sub

Nice and simple and it did just what I wanted it to do, until now!

Not looking for anything Read-Only, I just want this to work again.

What should it do:

If someone clicks the SAVE button, the dialog box will open, the extracted name will be inserted as the SaveAs file name, the user can save to their preferred location.

**royUK** · Mar 12th 2020

I'm using Office 365 and the code works fine for me. I don't think using the Before Save event is the best way to call this code. It will run everytime the user tries to save, Before Close would be better.

**Jack in the UK** · Mar 12th 2020

Code

Option Explicit
'// - jiuk - Add this code to the thisworkbook entry in the VBE and save
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strSaveAs As String
strSaveAs = ThisWorkbook.Worksheets("Monthly Payment Calc").Range("E2")
Application.Dialogs(xlDialogSaveAs).Show strSaveAs
End Sub
Private Sub Workbook_Open()
'// - jiuk - sometimes it is good to take a copy on open if it’s a critical workbook
'// - jiuk - maybe tag with date
Dim strSaveAs As String
strSaveAs = ThisWorkbook.Worksheets("Monthly Payment Calc").Range("E2") ' ** date here
Application.Dialogs(xlDialogSaveAs).Show strSaveAs
End Sub
'// jiuk - add this code to a standard module and save
'// jiuk - it’s good to have a on demand save that is automatic, and unconditional
Sub mySAVE_FILE_BACKUP()
'// jiuk - This code will fail if the workbook is new and unsaved, make sure saved as macro enabled
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\Backups\" & " " & Format(Now(), "dd-mmm-yyy hh mm AMPM") & _
ThisWorkbook.Name
End Sub

Display More

mlmaxey · Mar 13th 2020

royUK and jiuk,

Thank you both for the replies.

royUK,

The way we use the program at work, the consensus was to have it pop-up on any Save. I think we just got used to it. I may try to code something that saves a temporary copy somewhere but I just hate forcing people use a location I choose for them, but I also want to try to protect them from overwriting their 'Master', especially if I'm not around to give them a fresh copy. My skills, and time, aren't up to the task to do much more though.

jiuk,

Thanks for the other codes, I will incorporate them in my 'project', they will be very useful I think. However, I still can't save the Excel without it crashing on the first SaveAs. It's really strange, especially hearing that royUK had no issues. Makes me think there is something else hiding on me that I need to find and it might not necessarily be the code.

It is frustrating though when for the past 8 months it has worked a charm and now it is cumbersome, to say the least, to use.

**royUK** · Mar 13th 2020

Moving to Office 365 shouldn't usually cause issues. What was the previous version?

Share