Responses to SaveAs in Excel VBA

[wombat]

When you save to a filename already in the directory, a
message box gives three options: save, no save or cancel
(buttons "yes", "no" and "cancel"). In the case "yes"
it overwrites the file, but in the cases "no"
and "cancel" the code fails. How do you trap the latter
two conditions so that alternative actions can be offered
to the user? Also, if you choose "cancel" from the
SaveAs dialog can you trap that condition? I may have
missed something but the VBA helpfile seems to offer no
clue.

 

[Tony Woodhouse]

Wombat,

Try something similar to:-

Sub TestSave()
On Error Resume Next
ActiveWorkbook.SaveAs ("P:\hello.xls")
If Err.Number=1004 then
'user pressed No or Cancel - do something
End If
End Sub

It cannot differentiate between pressing "No" and pressing "Cancel" but i
guess this is good enough for your needs?