Changing "Cancel" in SaveAs

[universal]

Hello All,

Since Im employing the HidingWorksheets / ForcingMacros solution, I
have to ensure that on close the formats are restored and worksheets
Hidden/MadeVisible.

Im trying to force a SaveAs on close too, since the users will probably
have double-clicked as an attachment from an email and wont know to
save to the network.

Unfortunately, after pressing [x] and answering "Yes" to the prompt, if
they then cancel the SaveAs, the macro still runs to completion and
quits excel. Is there any way to make "Cancel" from the SaveAs dialog
Exit the Sub?

Many Thanks,
Eddie


Code below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim closercheck As Long

closercheck = MsgBox("Would you like to save before quitting Excel?",
vbInformation + vbYesNoCancel)

Select Case closercheck

Case Is = vbYes
Application.ScreenUpdating = False
Call restoremacros
Application.DisplayAlerts = False
Application.Dialogs(xlDialogSaveAs).Show
Application.Quit

Case Is = vbNo etc...

 

[kedarkulkarni]

i'm not sure if it helps
try using workbook's beforesave event.

add the condition
whenever usr want to save he must close the current workbook.

you can make sure the path where he saves by thisworkbook.fullpath

or whether he is using the saveas method then disallow him by making
cancel=true so he wont use saveas only and save changes to current
workbook only

 

[universal]

Perhaps I should just stick with:

ThisWorkbook.Save

Instead of:

Application.Dialogs(xlDialogSaveAs).Show


Any other ideas?

Many thanks,
E