Question re save box in Excel

  oo7juk 11:03 07 Sep 2007


I have a worksheet that I have protected apart from one cell. This cell contains a drop down menu wich triggers the various vlookups I have within the sheet. The sheet is basically read only so once you have your info you just exit. I can I stop the 'do you want to save changes' box from popping up. If this is not possible can you change the text within the box e.g. 'are you sure you want to exit'

Many thanks.

  silverous 11:21 07 Sep 2007

Hi 007juk,

Google is your friend! I searched for:

excel stop save prompt

and got:

click here

  VoG II 11:38 07 Sep 2007

Try this. Right click the Excel logo to the left of File on the menu bar > View Code. Paste in

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

  silverous 11:40 07 Sep 2007

Bit like the link above :)

  oo7juk 15:29 07 Sep 2007

Unfortunately the code didn't work. I have another object running on this workbook - is that why?

Can't see an option to insert new object...

Many thanks.

  silverous 16:17 07 Sep 2007

I think Vog's code is wrong, the code on the link I provided works if you / Vog had bothered to review it before posting !!!!!!

  VoG II 16:53 07 Sep 2007

Well with either code you insert it in the workbook's code module. You can have more than one in there, just not two with the same name.

  oo7juk 16:08 08 Sep 2007

No not working Silverous/VoG.

Tried copying and pasting into Thisworkbook, but nothimg. Also tried inserting module, but nothing either.

  VoG II 16:41 08 Sep 2007

Try this:

Press ALT + F11 to open the Visual Basic Editor. In the Project window, top left, right click on ThisWorkbook and select View Code. Paste in

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

then close the code window and try saving the file. Nothing should appear to happen as the code cancels the save operation.

I have tested this of course.

  oo7juk 23:21 09 Sep 2007

Hi VoG,

Managed to get your code to work, but only on a new worksheet. Doesn't seem to work on the one I'm working on...


  silverous 09:39 10 Sep 2007

The "Before save" code Vog provided didn't work for me, but the before close (from my link) did.

If you are happy to email a copy of your sheet I'd be happy to take a look.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?