I can not stop save warning message when I click Close (x) botton or Alt+F4

B

botcan

Hi experts!,

I have created a workbook with a macro (assigned o a button), which
saves the workbook under a different name when the macro button is
clicked based on the infromation entered by the end-user. Accordingly
I don't want the end-user to save that workbook under any other name
or before completely filing the information needed. The macro enables
saving only after all information is entered.

I have disabled all commands functions, bars etc. and shortcuts (such
as Ctr+S) to avoid end-user saving without cliciking the macro button.
All work fine except Close Button(X) of excel on the very up-right
corner of the screen and the ALT+F4 short-cut. On both cases excel
warns the end-user to save the workbook or not. I tried
"Application.DisplayAlerts = False" but it doesn't work at all
workbook-open or before close:
-------------------------------------------------------------------------------------
Private Sub Workbook_Open() ' I also tried before_close
'To disable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = False
End If
Next
Application.OnKey "^s", ""
Application.DisplayAlerts = False ' turns 'em off
End Sub
 
N

Nick Hodge

You should be able to cancel all user saves in a workbook_beforesave() event

Theory: If you get your code to set a hidden name or populate a cell with
text or similar when the button is pressed, the workbook_before save code
could check this and if it was there allow the save and delete the name/cell
value so that if the user uses the normal routes you can cancel the save
using the Cancel parameter in the event

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk
 
B

botcan

Thanks a lot for your reply Nick,
No way, it doesn' work either.
I forgot to say but I had tried that too:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.DisplayAlerts = False
End Sub

:( Any other idea?
 
N

Nick Hodge

What did you do exactly with my suggestion? The code you have below with
definitely not work. Application.DisplayAlerts just stops the application
showing them AND it takes the default answer. In the case of save yes/no the
default is yes.

If you can explain what you did with my suggestion, maybe we can help
develop it further?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk
 
B

botcan

What did you do exactly with my suggestion? The code you have below with
definitely not work. Application.DisplayAlerts just stops the application
showing them AND it takes the default answer. In the case of save yes/no the
default is yes.

If you can explain what you did with my suggestion, maybe we can help
develop it further?
Click to expand...
Dear Nick,

I'm extremely sorry but I couldn't understand your suggestion. I'm a
very newbee in programming excel and I'm not so much familiar with the
terminology (actually I'm a financial auditor). Could you please write
a sample code explaning your suggestion? The question (if I would
repeat), is how I can avoid people saving the workbook in any other
way (!), except for clicking the macro button in the worksheet (of
course only with the name assigned to the file by the macro) or
othervise just leave it as it is and go "off". I'm aware that the user
my change the name of the file afterwards (ie in windows explorer
through "rename" by right-click or etc. but I will handle it somehow.
I'm not thinking about that yet.

It's very clear: The code Application.DisplayAlerts would definitely
not work for my purposes, as in the case of save yes/no the default is
yes. Thanks very much for this and thanks for your kind interest and
help.

(I'm sorry for my English as it is not my native language, (neither
VBA :)).
 
D

Dave Peterson

Try this in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Please click the button to save!"
Cancel = True
End Sub

But you'll have to change your code that does the saving.

Option Explicit
sub YourSubNameHere()

'a bunch of code

'stop the _beforesave event from firing
application.enableevents = false
'your code that does the actual save.
application.enableevents = true

end sub

Remember that if the user disables macros -- or even disables events, then all
of this will fail.
 
B

botcan

Dear Dave,

Thanks for your interest and effort,

I tried only this part of your code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Please click the button to save!"
Cancel = True
End Sub
Click to expand...

on a new empty workbook (I just copied above text to Thisworkbook
Module). But I guess, I did something wrong. Now whenever I close
excel(!) and reopen it, no command bars, or right-click menus ever
appear again (probably related somehow with my previous macro). I know
it is nothing to do with your codes but I can not open any file
anymore nor can I close except for using close button(x). And that
time it asks "Please click the button to save!". Yeah definitly that
party works and it doesn't save anything at the end even though I give
a file name when it is asked by the second warning.

Now the problem is worse. First things first, please help me recover
my excel !! :)
 
B

botcan

Dear Dave,

Thanks for your interest and effort,

I tried only this part of your code:


on a new empty workbook (I just copied above text to Thisworkbook
Module). But I guess, I did something wrong. Now whenever I close
excel(!) and reopen it, no command bars, or right-click menus ever
appear again (probably related somehow with my previous macro). I know
it is nothing to do with your codes but I can not open any file
anymore nor can I close except for using close button(x). And that
time it asks "Please click the button to save!". Yeah definitly that
party works and it doesn't save anything at the end even though I give
a file name when it is asked by the second warning.

Now the problem is worse. First things first, please help me recover
my excel !! :)
Click to expand...

I decided to leave this saving matter (if and ever I can recover my
excel), because I understood I'm an idiot in excel programming which
needs real experience and training on this subject. I'm too far from
both of them :)).

I will appreciate if David, Nick or somebodyelse would help me recover
my excel and then I will leave it up.

Thanks to David and Nick for their immediate interests and replies.
 
D

Dave Peterson

The code you used in that new workbook didn't do the bad stuff. It just won't
let you save the workbook with that code in it without doing something special
(disabling events is one way).

But since you disable the toolbars in your other workbook, you have to turn on
what you turnned off (enable those controls) when your workbook closes--or even
when you switch workbooks.

One way to get all your toolbars back to factory defaults is this:

close excel
windows start button|search
look for *.xlb
Look in hidden folders, too.

Then move it, rename it to *.xlbOLD, or even delete it.

When you reopen excel, you'll have to change all the settings you want changed.

======
If you've customized lots and lots of toolbars, you may want to try running the
workbook_open event, but changed the enabled property from false to true.
 
N

Nick Hodge

I see Dave picked this up. I am not baling out,but as you appear to be
having other issues with Excel lets keep it to the other thread to maximize
responses

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk
 
B

botcan

Thanks to both of you. Everything is back and fine.
I will find sometime and go deep in excel programming and try to learn
it in due course.
I even didn't know what the meaning of 'event' was...
Anyway this was a great experience for me.

I wish you good luck with your studies. Thanks for your helps again!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Addin not allowing thisworkbook.save in before save event 3
Macro to open, Update links, save and close workbooks 0
VBA to Move Sheets and save 3
Auto Close Yes no button 3
Save as, not overwrite 1
Button looses Macro setting when running Macro 1
Save to CSV macro crashes Excel 2003 3
Closing without saving and without asking the user to save...or no 3

Top