testing for security level for running macros

[Martin Perera]

Hi Guys

I am attempting to test for the level of security set up in excel.
the reason is I am running a macro from the A:Drive and it is being setup on the hard drive.

This much i have sorted, but it wouldnt do anything if the macro sercurity is set above prompt.

What I intend is to test for security level, if it is too high then to set up a msgbox to inform user, and or on permission to change the security level. save the previous levels and reset them on exit.

I am researching it at the moment

any ideas.

cheers martin

 

[Oaktree]

If the security level is set to "high", your macro will never be launched, so you could never fire your message box to let them know. You also cannot access someone's security or modify it via code.

The commonly used workaround is to set up your workbook with just a sheet that has instructions for how to enable macros and hide (or xlveryhidden) the rest of your sheets. Then, with a workbook_open macro, hide your instructions sheet and unhide the rest of your sheets. That way, if macros are enabled, your workbook will work as it should and, if macros are disabled, your workbook will be useless, save for the instructions sheet.

 

[Martin Perera]

The commonly used workaround is to set up your workbook with just a sheet that has instructions for how to enable macros and hide (or xlveryhidden) the rest of your sheets. Then, with a workbook_open macro, hide your instructions sheet and unhide the rest of your sheets. That way, if macros are enabled, your workbook will work as it should and, if macros are disabled, your workbook will be useless, save for the instructions sheet.

wouldnt this require a way of determining if the macros are enabled or not?
is there an 'application.' to determine the situation so that the correct sheet/workbook would be visible?

or am i reading this wrong


martin

 

[Smitty]

Daniel Klann (dk) explains it in detail here.

In essence, if macros aren't enabled the code to unhide all of the sheets won't fire; if they are, sheets will be unhidden and the Enable Macros sheet won't be visible.

HTH,

Smitty

(Heya Mat!)

 

[TTom]

Just a thought (I'm dealing with same issue today)
Maybe just what was said?
Write a marco for Workbook_Open() that hides 'instruction sheet' and unhides the normal stuff. If macros don't run the user will end up on the instruction page on how to set macro level to medium. You might also try adding
Application.DisplayAlerts = False
on the open. I'm testing this today, so I'll let you know if I get it fully tweaked. Always learning... tema effots make it productive
TTom

 

[Martin Perera]

Now that does make sense,

literally an either / or

many thanks

will have a at that


martin

 

[TTom]

Great, however,
I forgot how the correct way to name a sheet as very hidden in my Workbook_Open() marco. I'm testing the idea from above.
And the answer is... ?

Actually, this will help me with setting the other workbook properties as required for a specified sheet's properties...
name of property and setting (true/false, xlVeryHidden, etc.)
Example on this too?
Brain fogged, TTom

 

[Smitty]

Something like this:

<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        ws.Visible = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Instructions" <SPAN style="color:#00007F">Then</SPAN> ws.Visible = xlSheetVeryHidden
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty

 

[TTom]

Not quite working for me, or at least me particular workbook, but I'll play with what you wrote and reply if I get results. Updates welcome... TTom

 

[Smitty]

Take a look at the link I gave for DK's site. He's got it all spelled out and coded. (Why recreate the wheel right?)

Smitty