Detect SaveAs Cancellation

[AnnetteTR]

Hi
I have a large spreadsheet that should be used for a construction.
To use it for the next construction I have made a macro called "NySag"
It selects the area with data for this construction and clears it, then it opens the Save As dialog in order to save the emptied spreadsheet in a new filename.
If the user cancels the saving I want the macro to undo the clearing - how can that be done?

My code is:

Sub NySag()
'
' Clears the sheet "Ventilation i rum" and save in new name
'
'
    Sheets("Ventilation i rum").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
Application.Dialogs(xlDialogSaveAs).Show
End Sub

ie how do I detect that the save as is cancelled and how do I undo the clearing?

Best regards
Annette

 

[pgc01]

Hi Annette

Try:

Dim bSave As Boolean
 
bSave = Application.Dialogs(xlDialogSaveAs).Show

If the user cancels or an error occurs you'll get a False return