Cause workbook to fail to open if user opts to disable macros
 

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

Cause workbook to fail to open if user opts to disable macros
 

Here's what I do....
Write code that does this:

Upon close....Hide all sheets: xlVeryHidden except one.

Upon Open...Unhide all sheets.

That way, if macros are disabled...all they see is the one sheet with a
message telling them that they need to enable macros to see the rest of the
workbook.

It's not bullet-proof....but, it's pretty good. :)
Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"I Believe" wrote:

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

correction....on save, not on close
 

The hiding code should engage when the workbook is SAVED.


***********
Regards,
Ron

XL2002, WinXP-Pro


"I Believe" wrote:

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

correction....on save, not on close
 

Thanks Ron, just implemented it and it works a treat! Also noticed users
can't unhide the sheets. Many thanks.

"Ron Coderre" wrote:

The hiding code should engage when the workbook is SAVED.


***********
Regards,
Ron

XL2002, WinXP-Pro


"I Believe" wrote:

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

correction....on save, not on close
 

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.
--
Don H.


"I Believe" wrote:

Thanks Ron, just implemented it and it works a treat! Also noticed users
can't unhide the sheets. Many thanks.

"Ron Coderre" wrote:

The hiding code should engage when the workbook is SAVED.


***********
Regards,
Ron

XL2002, WinXP-Pro


"I Believe" wrote:

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

correction....on save, not on close
 

You could just add more code to the Workbook.Save event to unhide the hidden
sheets so the user could keep working.

***********
Regards,
Ron

XL2002, WinXP


"donh" wrote:

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.
--
Don H.


"I Believe" wrote:

Thanks Ron, just implemented it and it works a treat! Also noticed users
can't unhide the sheets. Many thanks.

"Ron Coderre" wrote:

The hiding code should engage when the workbook is SAVED.


***********
Regards,
Ron

XL2002, WinXP-Pro


"I Believe" wrote:

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

correction....on save, not on close
 

Excel 2002 doesn't have an Autosave operation.

It has an Autorecovery operation but that doesn't save the workbook and
overwrite the workbook or trigger the BeforeSave event.

Autorecovery just saves a copy of the workbook which is deleted if Excel and the
workbook don't crash.


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 08:56:02 -0700, donh wrote:

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.

correction....on save, not on close
 

Thanks for the quick response, Ron. I think the gap here is that I was
assuming that the BeforeSave macro would hide the sheets and then return to
Excel, allowing it to continue the save operation. But I think that it's
possible to set a return parameter saying "don't do the save" to Excel -- and
then the macro itself could hide the sheets, issue a Save command, and then
unhide the sheets.

I'll play around with this to see if there is anything peculiar that happens
when it's a SaveAs instead of a Save. But the overall scheme seems sound.
Thanks again for the help.
--
Don H.


"Ron Coderre" wrote:

You could just add more code to the Workbook.Save event to unhide the hidden
sheets so the user could keep working.

***********
Regards,
Ron

XL2002, WinXP


"donh" wrote:

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.
--
Don H.


"I Believe" wrote:

Thanks Ron, just implemented it and it works a treat! Also noticed users
can't unhide the sheets. Many thanks.

"Ron Coderre" wrote:

The hiding code should engage when the workbook is SAVED.


***********
Regards,
Ron

XL2002, WinXP-Pro


"I Believe" wrote:

Hi All,
I need some help in programming my workbook to not open if the user tries to
open with macros disabled.
As you know, the user can set the security level to "high", "medium", or
"low". I would like my workbook to be able to react in the cases where the
level is set to "high" or "medium" because in these respectively, any macro
will be automatically disabled or the user prompted to opt to either enable
or disable all macros.
So, should the system automatically disable macros or the user ultimately
decide to disable macros when prompted, I need my workbook to react by
quickly closing (or not opening at all).
My idea was to check what security level is in place: if set to "high" then
force the workbook to immediately close again; but if set to "medium" then to
capture the user's response event when the system prompts the user, and close
the workbook if the choice was to disable macros.

Having taken 20mins to write the above succinctly, it has now occured to me
that if the system or user disables macros then my code to close the workbook
won't work anyway (!!)....or will it.
I guess I'm looking for any ideas - or confirmation that it simply can't be
done :[

Thanks and appreciation if you're still with me!

correction....on save, not on close
 

Gord, you may be correct about Excel 2002, but I'm using Excel 2003, and it
definitely has an autosave. In fact, we advise our users to leave it on, as
it means that no matter what bad thing happens to your machine, you will only
lose <n minutes of work. Many of our folks set it to about 10 minutes. As
you point out, Excel itself often recovers lost work with autorecovery, but
that isn't 100% reliable, I don't think.
--
Don H.


"Gord Dibben" wrote:

Excel 2002 doesn't have an Autosave operation.

It has an Autorecovery operation but that doesn't save the workbook and
overwrite the workbook or trigger the BeforeSave event.

Autorecovery just saves a copy of the workbook which is deleted if Excel and the
workbook don't crash.


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 08:56:02 -0700, donh wrote:

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.

correction....on save, not on close
 

If you're using autosave (not autorecovery!!) in xl2003, then you're using an
older copy of that autosave.xla addin.

Autosave didn't come with xl2003 or xl2002.

donh wrote:

Gord, you may be correct about Excel 2002, but I'm using Excel 2003, and it
definitely has an autosave. In fact, we advise our users to leave it on, as
it means that no matter what bad thing happens to your machine, you will only
lose <n minutes of work. Many of our folks set it to about 10 minutes. As
you point out, Excel itself often recovers lost work with autorecovery, but
that isn't 100% reliable, I don't think.
--
Don H.

"Gord Dibben" wrote:

Excel 2002 doesn't have an Autosave operation.

It has an Autorecovery operation but that doesn't save the workbook and
overwrite the workbook or trigger the BeforeSave event.

Autorecovery just saves a copy of the workbook which is deleted if Excel and the
workbook don't crash.


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 08:56:02 -0700, donh wrote:

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.



--

Dave Peterson

correction....on save, not on close
 

Gord and Dave,
I owe you both an apology: apparently, what I've always heard of as
"autosave" is actually labeled "autorecovery" -- this is what is doing the
automatic save every n minutes. I see that in the options form, the setting
says "Save Autorecovery information every ..."

I notice that the BeforeSave procedure does not get invoked when the
autorecovery save operation is performed. With respect to the initial issue
in this thread -- the desire to hide the workbook unless it is run with
macros on -- I'm not sure whether there is a large hole there that would be
saving the unhidden workbook in a file that could be later opened without
macors enabled. Have to do some testing.
--
Don H.


"Dave Peterson" wrote:

If you're using autosave (not autorecovery!!) in xl2003, then you're using an
older copy of that autosave.xla addin.

Autosave didn't come with xl2003 or xl2002.

donh wrote:

Gord, you may be correct about Excel 2002, but I'm using Excel 2003, and it
definitely has an autosave. In fact, we advise our users to leave it on, as
it means that no matter what bad thing happens to your machine, you will only
lose <n minutes of work. Many of our folks set it to about 10 minutes. As
you point out, Excel itself often recovers lost work with autorecovery, but
that isn't 100% reliable, I don't think.
--
Don H.

"Gord Dibben" wrote:

Excel 2002 doesn't have an Autosave operation.

It has an Autorecovery operation but that doesn't save the workbook and
overwrite the workbook or trigger the BeforeSave event.

Autorecovery just saves a copy of the workbook which is deleted if Excel and the
workbook don't crash.


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 08:56:02 -0700, donh wrote:

I've the same issue as "I Believe" -- this seems to be a recurring theme. It
would seem that the nice way to handle this would be for Microsoft to allow
an option on a workbook that says "Don't open this workbook if the user
doesn't enable macros" -- but that's another fish to fry.

Ron, your solution seems clever and fine, but with one gap that I don't see
how to fix: If the macro (that hides all sheets but one) runs before a save,
presumably using the WorkbookBeforeSave event, then how does one then unhide
the sheets to allow the user to continue working? I'm presuming that this
WorkbookBeforeSave event also gets triggered during autosave, in the middle
of a working session. There doesn't seem to be a WorkbookAfterSave event
that would allow one to unhide everything.



--

Dave Peterson