SaveAs on opening workbook

[AusSteelMan]

Hi everyone,

I would like to have the SaveAs dialogue box appear when the workbook is opened.

I do not need a cell value to become the name or anything like that.

The ability to click Cancel should still exist.

The purpose is to prompt the user to save as a new name before altering the existing data.

I had a quick trawl but couldn't really find what I needed.

Thanks to anyone who can help out

 

[Blade Hunter]

This what you want?

http://www.mrexcel.com/archive/VBA/5473.html

Cheers

Dan

 

[mikerickson]

You could put this in the ThisWorkbook code module.

Private Sub Workbook_Open()
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

Or when you Save your file As, you could use Options to select Always Create Backup and Excel will do it automatically without code.

 

[AusSteelMan]

Hi guys

Thanks to both of you again.

Blade,
The linked page had code I altered since I did not want it to provide a new name.

mike,
That code didn't appear to work. When I used the line to open the dialogue box in the immediate window, it worked, but did nothing when the book opened.

I ended up with a compromise:

Sub Autpen()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

This appears to work OK. Is there a difference between Autpen and Workbook_Open?

Thanks again.
Darren

 

[Blade Hunter]

Were you putting the workbook open one at the workbook level in the VBE? Using the auto_open is a very old method, I wasn't aware that it still worked to be honest.

 

[AusSteelMan]

Dan,

No I was a goose and used it in a regular module.

Of course when in This Workbook, mike's works correctly (like I thought it would)

Sorry for wasting your time on something I should have done properly in the first place (but I appreciate it anyway!)

Cheers,
Darren

 

[Blade Hunter]

It's no waste if it now works .

Glad I could help

Dan

 

[mikerickson]

Have you looked at the Automatic Save Backup setting?