Saving a workbook using VBA
 

I am trying to set up my workbook to save any changes on pressing of a button.

I have created the macro, which is simple and contains the one following line

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name

This quite expectedly shows up a dialogue box asking if I want to overwrite
the existing file.

If I select Yes,then the file saves without any problem. However if I say
no or cancel then it produces the following error - Run-time error 1004,
application-defined or object defined error.

Ideally if I select no then I would want a dialogue that allows me to change
the filename, or if I select Cancel then I would like it to just return to
the current active worksheet.

Ive been playing with this for days and I am no closer to solving it.

Help anyone please

Saving a workbook using VBA
 

Try disabling alerts

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name
Application.DisplayAlerts = True

Mike

"Michael Hudston" wrote:

I am trying to set up my workbook to save any changes on pressing of a button.

I have created the macro, which is simple and contains the one following line

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name

This quite expectedly shows up a dialogue box asking if I want to overwrite
the existing file.

If I select Yes,then the file saves without any problem. However if I say
no or cancel then it produces the following error - Run-time error 1004,
application-defined or object defined error.

Ideally if I select no then I would want a dialogue that allows me to change
the filename, or if I select Cancel then I would like it to just return to
the current active worksheet.

Ive been playing with this for days and I am no closer to solving it.

Help anyone please

Saving a workbook using VBA
 

Why not just

ActiveWorkbook.Save

--
__________________________________
HTH

Bob

"Michael Hudston" wrote in
message ...
I am trying to set up my workbook to save any changes on pressing of a
button.

I have created the macro, which is simple and contains the one following
line

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name

This quite expectedly shows up a dialogue box asking if I want to
overwrite
the existing file.

If I select Yes,then the file saves without any problem. However if I say
no or cancel then it produces the following error - Run-time error 1004,
application-defined or object defined error.

Ideally if I select no then I would want a dialogue that allows me to
change
the filename, or if I select Cancel then I would like it to just return to
the current active worksheet.

Ive been playing with this for days and I am no closer to solving it.

Help anyone please

Saving a workbook using VBA
 

I have tried that bob, but makes no difference

"Bob Phillips" wrote:

Why not just

ActiveWorkbook.Save

--
__________________________________
HTH

Bob

"Michael Hudston" wrote in
message ...
I am trying to set up my workbook to save any changes on pressing of a
button.

I have created the macro, which is simple and contains the one following
line

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name

This quite expectedly shows up a dialogue box asking if I want to
overwrite
the existing file.

If I select Yes,then the file saves without any problem. However if I say
no or cancel then it produces the following error - Run-time error 1004,
application-defined or object defined error.

Ideally if I select no then I would want a dialogue that allows me to
change
the filename, or if I select Cancel then I would like it to just return to
the current active worksheet.

Ive been playing with this for days and I am no closer to solving it.

Help anyone please

Saving a workbook using VBA
 

Ok, Well this got rid of the error, however now I dont have any choice at
all whether it saves using the current name or allowing a re-name.

"Mike H" wrote:

Try disabling alerts

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name
Application.DisplayAlerts = True

Mike

"Michael Hudston" wrote:

I am trying to set up my workbook to save any changes on pressing of a button.

I have created the macro, which is simple and contains the one following line

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name

This quite expectedly shows up a dialogue box asking if I want to overwrite
the existing file.

If I select Yes,then the file saves without any problem. However if I say
no or cancel then it produces the following error - Run-time error 1004,
application-defined or object defined error.

Ideally if I select no then I would want a dialogue that allows me to change
the filename, or if I select Cancel then I would like it to just return to
the current active worksheet.

Ive been playing with this for days and I am no closer to solving it.

Help anyone please

Saving a workbook using VBA
 

Hi Michael,

Just pop some error handling around your code:

On Error GoTo ErrorHandler:
ActiveWorkbook.SaveAs ActiveWorkbook.Name
ErrorHandler:

Thanks,

Steve