Save - Yes / No / Cancel

sparx · #1 05-06-2006, 12:58 PM

Hello all, I have found some vba that may " Force users to enable macro's ". A workbook using the vba starts up and asks enable or disable macro's at startup. If you select disable, all sheets are hidden except one that says about your macro status. If you select enable, then the one about your macro status is hidden and all the other sheets are visable using the very-visable routine. This is a great idea if you wish somebody to run your file as it makes them run the file macro's and all works well - this is until you might already have a before_save routine then the file just loops and loops saying "overwrite existing file? Y/N". So my question is, when you run this file, and you enable macro's, edit some pages, then go to close, your prompt with the SaveAs routine that saves the file - but then you press the Close and then the macro hides all worksheets and displays the macro status page but because its done that again says "overwrite existing file? Y/N" - instead of this box appearing - is there a macro that can be added to the before_close routine that automatically selects "Yes" for you and does not give you the option to press No or Cancel. - If anyone would like the vba then I can add this.

#2 05-06-2006, 01:10 PM

Change .SaveAs to .Save, and in your _BeforeSave procedure, wrap the .Save
between lines like these:

Application.EnableEvents = False
' do the save here
Application.EnableEvents = True

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"sparx" <sparx.27eaxy_1146934802.0735@excelforum-nospam.com> wrote in
message news:sparx.27eaxy_1146934802.0735@excelforum-nospam.com...
>
> Hello all, I have found some vba that may " Force users to enable
> macro's ". A workbook using the vba starts up and asks enable or
> disable macro's at startup. If you select disable, all sheets are
> hidden except one that says about your macro status. If you select
> enable, then the one about your macro status is hidden and all the
> other sheets are visable using the very-visable routine. This is a
> great idea if you wish somebody to run your file as it makes them run
> the file macro's and all works well - this is until you might already
> have a before_save routine then the file just loops and loops saying
> "overwrite existing file? Y/N". So my question is, when you run this
> file, and you enable macro's, edit some pages, then go to close, your
> prompt with the SaveAs routine that saves the file - but then you press
> the Close and then the macro hides all worksheets and displays the macro
> status page but because its done that again says "overwrite existing
> file? Y/N" - instead of this box appearing - is there a macro that can
> be added to the before_close routine that automatically selects "Yes"
> for you and does not give you the option to press No or Cancel. - If
> anyone would like the vba then I can add this.
>
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile:
> http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=539543
>

#3 05-06-2006, 01:10 PM

I'm sorry but your info is just wrong. You cannot force anybody to enable macros or make a procedure run in a workbook if they have
turned off (disabled) macros before opening a file. That is the whole reason for giving people that option. Without the ability to
disable all code in Excel, there would be no end to the viruses and problems. Your code cannot force anybody to do what you say.

Sorry
--
RMC,CPA

"sparx" <sparx.27eaxy_1146934802.0735@excelforum-nospam.com> wrote in message
news:sparx.27eaxy_1146934802.0735@excelforum-nospam.com...

Hello all, I have found some vba that may " Force users to enable
macro's ". A workbook using the vba starts up and asks enable or
disable macro's at startup. If you select disable, all sheets are
hidden except one that says about your macro status. If you select
enable, then the one about your macro status is hidden and all the
other sheets are visable using the very-visable routine. This is a
great idea if you wish somebody to run your file as it makes them run
the file macro's and all works well - this is until you might already
have a before_save routine then the file just loops and loops saying
"overwrite existing file? Y/N". So my question is, when you run this
file, and you enable macro's, edit some pages, then go to close, your
prompt with the SaveAs routine that saves the file - but then you press
the Close and then the macro hides all worksheets and displays the macro
status page but because its done that again says "overwrite existing
file? Y/N" - instead of this box appearing - is there a macro that can
be added to the before_close routine that automatically selects "Yes"
for you and does not give you the option to press No or Cancel. - If
anyone would like the vba then I can add this.

--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=539543

#4 05-06-2006, 02:30 PM

How about using a helper workbook that uses a macro to open the real workbook.
If macros are disabled, then the real workbook won't open.

If macros are enabled, then the helper workbook can open the real workbook--and
macros will be enabled.

"R. Choate" wrote:
>
> I'm sorry but your info is just wrong. You cannot force anybody to enable macros or make a procedure run in a workbook if they have
> turned off (disabled) macros before opening a file. That is the whole reason for giving people that option. Without the ability to
> disable all code in Excel, there would be no end to the viruses and problems. Your code cannot force anybody to do what you say.
>
> Sorry
> --
> RMC,CPA
>
> "sparx" <sparx.27eaxy_1146934802.0735@excelforum-nospam.com> wrote in message
> news:sparx.27eaxy_1146934802.0735@excelforum-nospam.com...
>
> Hello all, I have found some vba that may " Force users to enable
> macro's ". A workbook using the vba starts up and asks enable or
> disable macro's at startup. If you select disable, all sheets are
> hidden except one that says about your macro status. If you select
> enable, then the one about your macro status is hidden and all the
> other sheets are visable using the very-visable routine. This is a
> great idea if you wish somebody to run your file as it makes them run
> the file macro's and all works well - this is until you might already
> have a before_save routine then the file just loops and loops saying
> "overwrite existing file? Y/N". So my question is, when you run this
> file, and you enable macro's, edit some pages, then go to close, your
> prompt with the SaveAs routine that saves the file - but then you press
> the Close and then the macro hides all worksheets and displays the macro
> status page but because its done that again says "overwrite existing
> file? Y/N" - instead of this box appearing - is there a macro that can
> be added to the before_close routine that automatically selects "Yes"
> for you and does not give you the option to press No or Cancel. - If
> anyone would like the vba then I can add this.
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=539543

--

Dave Peterson

sparx · #5 05-06-2006, 02:41 PM

Hi again, follow the link to see what I mean regards forcing enabling macro's ( It does NOT actually force you to enable macro's ) it simply saves the file with a specific worksheet open and hides all your other sheets - so when you next open the file and you select disable - then you just switched off macro's and one of the macro's that makes all worksheets visable is in that file - so you cant see the worksheets of your file - great!!

http://www.danielklann.com/excel/for...be_enabled.htm

There is a download at the bottom of this webpage - it works a treat.

Sorry if I confused anybody reading my original post - when you disable macro's - you disable macro's - this file does NOT switch them back on if you disabled them at file startup.

Thanks for reply to my query - what wahappening in my file is this.
I have a beforesave function and now a beforeclose function - if you save your file, it saves OK - if you then select close - it runs some hide sheet macro - then the file just changed so then are asked to overwrite existing file - you select yes and it keeps on going. The answer to my question stops this from happening - this is the modified vba.

Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.name <> "Sheet1" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True

Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True

End Sub

Thanks

sparx · #6 05-06-2006, 02:46 PM

I just read my own reply - and I dont normally sound like a cartoon character - I was meant to say "Whats happening in my" and so on!! not what wahappening....................

#7 05-06-2006, 03:00 PM

But it won't do any of that if macros are disabled before the file is opened. With disabled macros, nothing will automatically
happen. People have wanted to circumvent this for years, but the ability to disable VBA code before a file is opened is the front
line of defense against malicious code. I saw your code, but if the person has code disabled to start with, none of your code will
do anything no matter how it is written.
--
RMC,CPA

"sparx" <sparx.27efsy_1146941101.1343@excelforum-nospam.com> wrote in message
news:sparx.27efsy_1146941101.1343@excelforum-nospam.com...

Hi again, follow the link to see what I mean regards forcing enabling
macro's ( It does NOT actually force you to enable macro's ) it simply
saves the file with a specific worksheet open and hides all your other
sheets - so when you next open the file and you select disable - then
you just switched off macro's and one of the macro's that makes all
worksheets visable is in that file - so you cant see the worksheets of
your file - great!!

http://www.danielklann.com/excel/for...be_enabled.htm

There is a download at the bottom of this webpage - it works a treat.

Sorry if I confused anybody reading my original post - when you disable
macro's - you disable macro's - this file does NOT switch them back on
if you disabled them at file startup.

Thanks for reply to my query - what wahappening in my file is this.
I have a beforesave function and now a beforeclose function - if you
save your file, it saves OK - if you then select close - it runs some
hide sheet macro - then the file just changed so then are asked to
overwrite existing file - you select yes and it keeps on going. The
answer to my question stops this from happening - this is the modified
vba.

Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.name <> "Sheet1" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True

Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True

End Sub

Thanks

--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=539543

sparx · #8 05-06-2006, 04:01 PM

Hi, R. Choate. You are right - when you disable macro's at startup - they wont work - but if they dont work then fine - the file dont work but if they chose enable then one of the macro's ( on file close - not save ) switches off all worksheets and makes only one display - then you are asked to save - you select YES and you have just completed the loop. When you next open the file if you select disable - the file opens but to the last state the file saved in which was - the single sheet being viewed. So guess what - the file cant display any other worksheets because you didnt enable macro's. Why dont you download the file as described elsewhere in these notes and see the function working for yourself.
Sparx

#9 05-06-2006, 05:50 PM

Wrap your Save line in these two lines

Application.DisplayAlerts = False

' your code here

Application.DisplayAlerts = True

Gord Dibben MS Excel MVP

On Sat, 6 May 2006 11:58:36 -0500, sparx
<sparx.27eaxy_1146934802.0735@excelforum-nospam.com> wrote:

>
>Hello all, I have found some vba that may " Force users to enable
>macro's ". A workbook using the vba starts up and asks enable or
>disable macro's at startup. If you select disable, all sheets are
>hidden except one that says about your macro status. If you select
>enable, then the one about your macro status is hidden and all the
>other sheets are visable using the very-visable routine. This is a
>great idea if you wish somebody to run your file as it makes them run
>the file macro's and all works well - this is until you might already
>have a before_save routine then the file just loops and loops saying
>"overwrite existing file? Y/N". So my question is, when you run this
>file, and you enable macro's, edit some pages, then go to close, your
>prompt with the SaveAs routine that saves the file - but then you press
>the Close and then the macro hides all worksheets and displays the macro
>status page but because its done that again says "overwrite existing
>file? Y/N" - instead of this box appearing - is there a macro that can
>be added to the before_close routine that automatically selects "Yes"
>for you and does not give you the option to press No or Cancel. - If
>anyone would like the vba then I can add this.

#10 05-06-2006, 09:25 PM

What you should have said was "Encourage" (not "Force") people to enable
macros, then clarify that without macros enabled, the useful parts of the
workbook never become visible.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"sparx" <sparx.27ejqm_1146946200.9546@excelforum-nospam.com> wrote in
message news:sparx.27ejqm_1146946200.9546@excelforum-nospam.com...
>
> Hi, R. Choate. You are right - when you disable macro's at startup -
> they wont work - but if they dont work then fine - the file dont work
> but if they chose enable then one of the macro's ( on file close - not
> save ) switches off all worksheets and makes only one display - then
> you are asked to save - you select YES and you have just completed the
> loop. When you next open the file if you select disable - the file
> opens but to the last state the file saved in which was - the single
> sheet being viewed. So guess what - the file cant display any other
> worksheets because you didnt enable macro's. Why dont you download the
> file as described elsewhere in these notes and see the function working
> for yourself.
> Sparx
>
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile:
> http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=539543
>

Welcome to the Excel Forum

Forum Statistics