Force SaveAs regardless of button pressed - causing crash

  • 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)

    1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    2. Dim strSaveAs As String
    3. strSaveAs = ThisWorkbook.Worksheets("Monthly Payment Calc").Range("E2")
    4. Application.Dialogs(xlDialogSaveAs).Show strSaveAs
    5. 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.

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

  • royUK and jiuk,

    Thank you both for the replies.


    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.


    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.