Cancel Button on file save

[lwm]

Using this code


NewName = InputBox(prompt:="File Name and Directory", Default:=FName)

ActiveWorkbook.SaveAs Filename:=NewName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False, AddToMru:=True

When the user presses the cancel key the macro fails. What do I add to get
it to just end the sub.

Thanks

 

[Dave Peterson]

You could just overwrite any existing file:

newname = inputbox(...)

application.displayalerts = false
activeworkbook.saveas ...
application.displayalerts = true

to allow the user to cance if there's an existing file:

newname = inputbox(...)
on error resume next
activeworkbook.saveas ...
if err.number <> 0 then
msgbox "something bad happened"
err.clear
end if
on error goto 0

'keep going....

==========
You may want to look at application.getsaveasfilename in VBA's help. It can
eliminate a lot of typing errors by the user and a lot of validation checks for
you the developer. (Instead of an inputbox.)

 

[lwm]

Thanks Dave

I am doing a begginer class for non techical peolpe on simple macros so I am
trying to use recorded macro's and them make minor easy to explain changes.


Which means it is not so easy for me sometimes.

 

[Dave Peterson]

One common task is to open an existing file or to save a file with a new name.

You can record the action, but that recorded macro will have the names embedded
into the recorded macro.

One of the things you may want to include in your task is a way of getting the
name of the file to open (application.getopenfilename) and a way of getting the
name of file to save (application.getsaveasfilename).

Those commands can be incorporated into recorded code that really increases the
functionality of the macro.