How to quit the application if user select "Disable macro"?

  • Thread starter Thread starter yongwei
  • Start date Start date
Y

yongwei

I tried to force run macro, but it said there is no way to do it, so my
second question is how to terminate the application if the macro is
disabled?
I have an excel file in which cells are need to proected by disable the
selection. Is there a way to disable selection from UI? I know I can lock
and hide it.
Thanks in advance!!
 
Here is a general approach that I have used:

Create a workbook that only shows one sheet with the instructions: "Close
this workbook and reopen it with Macros enabled". Use code to hide this
sheet and "reveal" the rest of the workbook when the book is opened. Only
by opening the book with macros enabled can anyone see anything other than
your opening message. Reverse the hide/reveal process when closing so that
the book is always saved in the "message only" state.

1) Create your "message" sheet.
2) add code to Workbook_Close that will make the message sheet visible and
ALL OTHERS "very hidden". Force a workbook save at this point. This can be
controversial and/or tricky.
3) add code to the Workbook_Open event that will "very hide" the message
sheet and make all others visible.

This way, when closed from a macro-enabled machine, it will ALWAYS save with
only the message showing. If it is opened with macro disabled, that is all
anyone will see. If it opened with macros enabled, the "true" workbook
becomes visible and hides itself again before exiting.

Hope this helps,
 
it will ALWAYS save with only the message showing


Not quite. A Save As doesn't fire the Workbook_BeforeClose() event,
so saving the file with the sheets visible, then doing a Save As to
a different file name (as when, say, the user wants to save a
particular scenario) leaves the original file saved with the sheets
exposed.

While I too have used similar approaches, trying to secure a
workbook generally amounts building Ptolemaic "epicycles on
epicycles".
 
A Save As doesn't fire the Workbook_BeforeClose() event,
Click to expand...
..

It does in Excel 97 (8). When did Microsoft 'fix' this?

[Reposted with correct subject line.]
 
Hmm..

It doesn't fire in MacXL98 (8)/01 (9)/v.X (10). I'll ask the devs...

I guess that since I "knew" it didn't work on the Mac side, I never
bothered with it in WinXL.
 
J.E. McGimpsey said:
It doesn't fire in MacXL98 (8)/01 (9)/v.X (10). I'll ask the devs...

I guess that since I "knew" it didn't work on the Mac side, I never
bothered with it in WinXL.
Click to expand...

My mistake. Save As fires BeforeSave, not BeforeClose. But BeforeSave and
BeforeClose could call the same subroutine to do what George Nicholson
outlined.
 
Almost, but not quite. Since the event is fired before the save, the
designer will need to have some way of restoring the sheets after
the save. And since the user can cancel prior to a save, that almost
certainly means that the save will have to be handled within the
Before_Save macro, then set Cancel to True.

Epicycles on epicycles.

And while that will meet the design criteria, it will be very
counterintuitive to the user, who does a Save As to preserve a
particular scenario, then restores from the original, only to find
that the original has been modified as well.

Still doesn't mean much, when a simple hex editor can list all the
sheet names, even if they're xlVeryHidden.
 
J.E. McGimpsey said:
Almost, but not quite. Since the event is fired before the save, the
designer will need to have some way of restoring the sheets after
the save. And since the user can cancel prior to a save, that almost
certainly means that the save will have to be handled within the
Before_Save macro, then set Cancel to True.

Epicycles on epicycles.
Click to expand...

It's not uncommon to save in BeforeSave even handlers and set Cancel to
true. It's the only way to perform some tasks. Epicycles are in the eye of
the beholder. FWLIW, this may be one of the few things Lotus did better in
123 - 123's object model has AfterSave and AfterPrint event handlers. No
AfterClose though.
And while that will meet the design criteria, it will be very
counterintuitive to the user, who does a Save As to preserve a
particular scenario, then restores from the original, only to find
that the original has been modified as well.
Click to expand...

?

If all the macro called by BeforeSave and BeforeClose does is hide
interesting worksheets while displaying message worksheets, and if copied
workbooks had Open event handlers to reverse this, what's the problem?
Still doesn't mean much, when a simple hex editor can list all the
sheet names, even if they're xlVeryHidden.
Click to expand...

Granted. For that matter, any other workbook with macros enabled could
iterate through this other workbook's worksheets unhiding all of them.
Workbook protection would be easy enough to eliminate.
 
Back
Top