force macro

  • Thread starter Thread starter Fiona
  • Start date Start date
F

Fiona

Is it possible to disable a workbook if the user decides not to enable
macros ?

Obviously if macros are disabled then I can't use macro code :o) but
then the workbook hasn't loaded at that point anyway, or has it ?

Erm? ;O| Well is it possible ?
 
Fiona,

Usual way is to put a message on one sheet that says that the user must run
with Macros enabled. Hide all the other sheets (use xlVeryHidden so that it
cannot be changed in Excel). In the Workbook_Open event add some code to
hide that sheet, and un hide all others. If they don't enable macros, your
macro won't fire, so they only see the warning sheet.

It isn't foolproof, but it should help.

Oh, by the way, add code in the Before_Close event to unhide the warning
sheet, and very hide the others.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
the workbooks load whether macros are disabled or not. There is no setting
that would change the loading process based on the users response to the
question to disable macros. If he workbook is selected to be opened, it
will be opened particularly if macros are disabled (nothing can act to close
it).

You can put your code in an addin, then the user can elect to load your
addin and will not be prompted each time.

In xl2000 or later, you can certify your code and the user can select you as
a trusted source. Subsequent openings will not prompt.

The popular alternate suggestion is to hide all your sheets with a single
sheet showing with a message that functionality is not avaiable unless the
user chooses to enable macros. Then in the workbook_open event, there is
code to make the workbook functional and in the workbook_beforeclose event
restore it to its single sheet form. With the limited protection afforded
workbooks, this is easily defeated, but may be satisfactory in your
situation.
 
Thanks Everyone for the help, Veryhiddensheets then.

Jeff, your option 1) of protecting WB/WS adds a little extra but when
I protect my WB it loads restored rather than maximised, any ideas ?
Sheet protection is OK but if I try to protect the WB too it just
loads restored?
 
It looks like the window is restored (not maximized) if you select Protect Windows. If you only select Protect Structure the windows work OK. You probably don't want to protect the display windows in this case anyway..

Workbook-level protection only keeps users from changing the order of sheets (structure) or window display. It doesn't affect the data. To protect data, you want Worksheet-level protection.
 
Back
Top