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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

botcan - 24 Mar 2007 19:54 GMT

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
--------------------------------------------------------------------------------------
What should I do? Please somebody help me!!!!
Please also consider that the end-user would be able to save through
clicking the macro button.
Thanks and regards in advance....

Reply to this Message

Nick Hodge - 24 Mar 2007 22:43 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

> Hi experts!,
>
[quoted text clipped - 29 lines]
> clicking the macro button.
> Thanks and regards in advance....

Reply to this Message

botcan - 25 Mar 2007 02:55 GMT

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?

Reply to this Message

Nick Hodge - 25 Mar 2007 09:19 GMT

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?

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

> Thanks a lot for your reply Nick,
> No way, it doesn' work either.
[quoted text clipped - 6 lines]
>
> :( Any other idea?

Reply to this Message

botcan - 25 Mar 2007 10:24 GMT

On 25 Mart, 11:19, "Nick Hodge"
<nick_hodgeTAKETHIS...@zen.co.uk.ANDTHIS> wrote:

> What did you do exactly with my suggestion? The code you have below with
> definitely not work. Application.DisplayAlerts just stops the application
[quoted text clipped - 3 lines]
> If you can explain what you did with my suggestion, maybe we can help
> develop it further?

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 :)).

Reply to this Message

Nick Hodge - 25 Mar 2007 20:04 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

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

> On 25 Mart, 11:19, "Nick Hodge"
> <nick_hodgeTAKETHIS...@zen.co.uk.ANDTHIS> wrote:
[quoted text clipped - 29 lines]
> (I'm sorry for my English as it is not my native language, (neither
> VBA :)).

Reply to this Message

botcan - 25 Mar 2007 21:02 GMT

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!!

Reply to this Message

botcan - 25 Mar 2007 21:30 GMT

I just noticed that Nick's webpage (www.nickhodge.co.uk) is a good
start for me.
Sorry Nick I didn't notice that before.

Reply to this Message

Dave Peterson - 25 Mar 2007 22:36 GMT

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

> Thanks to both of you. Everything is back and fine.
> I will find sometime and go deep in excel programming and try to learn
[quoted text clipped - 3 lines]
>
> I wish you good luck with your studies. Thanks for your helps again!!

Signature

Dave Peterson

Reply to this Message

botcan - 25 Mar 2007 22:54 GMT

> You can read more about events at:
> Chip Pearson's site:http://www.cpearson.com/excel/events.htm
[quoted text clipped - 12 lines]
>
> Dave Peterson

Thanks Dave I will check them also.

Reply to this Message

Dave Peterson - 25 Mar 2007 14:33 GMT

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.

> Hi experts!,
>
[quoted text clipped - 29 lines]
> clicking the macro button.
> Thanks and regards in advance....

Signature

Dave Peterson

Reply to this Message

botcan - 25 Mar 2007 18:41 GMT

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

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 !! :)

Reply to this Message

botcan - 25 Mar 2007 18:52 GMT

> Dear Dave,
>
[quoted text clipped - 20 lines]
> Now the problem is worse. First things first, please help me recover
> my excel !! :)

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.

Reply to this Message

Dave Peterson - 25 Mar 2007 19:35 GMT

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.

> Dear Dave,
>
[quoted text clipped - 20 lines]
> Now the problem is worse. First things first, please help me recover
> my excel !! :)

Signature

Dave Peterson

Reply to this Message