Stopping the save function...until a user has clicked "save as".

**ExcelToyney** · 02-26-2010, 06:58 AM

Hello folks,

Your favourite nooby here.

Basically I want to block the 'save' function of an excel workbook. Being a template I want the user to have to Save As, to keep the original template in tact, then they can do whatever they want with it once it's on their local system.

Stopping the save function and displaying a nice message is part 1 of the issue.

Please Login or Register  to view this content.

This works perfectly for that....however, once they have renamed, they STILL need to "Save As" after, which is annoying.

Is there something I can add to this code to stop this once the user has changed the name of the workbook in someway?

I was thinking something along the lines of:

(This is in Toyneycode, the language of my brain and in no way affiliated with Excel!)

Please Login or Register  to view this content.

In my brain, as soon as they "saved as" and therefore changed the name of the workbook, the code would realize this and allow them to 'save' as normal again!

Or am I just being too hopeful!

Thanks in advance

Toyney

**Marcol** · 02-26-2010, 07:16 AM

Try this

Please Login or Register  to view this content.

Or a variation to suit

**dominicb** · 02-26-2010, 07:22 AM

Good morning ExcelToyney

If you have no other event driven code, then just use this line after the user has used Save As :

Please Login or Register  to view this content.

This will simply disable the event that fires when the user attempts to save - so the user doesn't get the warning.

HTH

DominicB

**ExcelToyney** · 02-26-2010, 08:06 AM

I've just realised that doesn't work perfectly.

For example, if somebody tries to save, it will bring up that message and they will probably save as. However, if it brings up the message and they then quit the spreadsheet, it then prompts them to save the sheet. As the code has stopped working.

Basically I need the sheet to not allow them to just 'save' until they have saved as something different, when they have, then they can save again.

**Marcol** · 03-01-2010, 07:36 AM

Try this ------- (or a variation to suit your needs)

Please Login or Register  to view this content.

It's not bullet proof - the user could overwrite in the saveas dialog box response

"The file Template.xls already exists. Do you want to replace the existing file?"

Or save in a different location

Just how hard do you want try to stop wrong input?