Save As (VBA) stopped working

[SueBK]

I have code in a macro to prompt the user to save the file, followed by a message box to say the task is complete.

    MsgBox ("Please save the report")
    file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
    If file_name <> False Then
    ActiveWorkbook.SaveAs Filename:=file_name
    MsgBox "File Saved!"
    End If

    MsgBox ("All worksheets have now been created. Thank you.")

It has worked beautifully to date.

Today!!!! I get the "Please save the report" message, and then straight away the "all complete" message box and the whole *&^#@) thing hangs for ages. THEN, I can edit and make changes to my UNSAVED file, but I can't access the Home menu to save the dang thing and eventually it just falls over.

 

[SueBK]

So, let me see if I know what's going on here. I save the first piece of code 'as is'. Then where I'm currently using "UsedRange" I would use "myUsedRange" instead, and include the dimming & setting lines?

Bring on the three day weekend and a bit of VBA oblivion. And add "public functions" to my "read up about on Monday" list.

 

[MARK858]

Then where I'm currently using "UsedRange" I would use "myUsedRange" instead, and include the dimming & setting lines

You would use "myUsedRange" instead of "UsedRange" but no you don't need to include the dimming and setting lines as they are defined in the function.

Sub FindUsedRange()

    If myUsedRange Is Nothing Then
        MsgBox "There is no used range, the worksheet is empty."
    Else
        MsgBox "The real used range is: " & myUsedRange.Address
    End If

End Sub