Need Macro Help - to change Macro Security to Low

[Seanzies]

I have a spreadsheet with Macros in it. But in order for them to run your macro security level has to be set to low. Is there a way with Autpen, to change the macro security to low first and automatically and then then macros will load in the spreadsheet. I know that sometimes you need to open excel first, change the macro security, then open the spreadsheet with the macros. I am trying to save some steps for users because thousands of people will be using this and I don't want to get calls from them saying the macros are not working because they didn't read my instructions.
Can anyone help????

 

[Travis]

No this cannot be done. Macro security is there so malicious code cannot be run by unsuspecting users. If it could be changed with code then there would be no point in having security.

 

[bandit_1981]

If you use a launch program this can be done. You make a small exe they click on and it loads the spreadsheet for them. During the execution of the exe and before the launch you can change the macro secruity settings by editing the registry keys. Set it to low and the spread sheet opens fine and on workbook close re-edit the key to put it back to what it was. Ofcorse this is tricky becuase if your workbook crashes they have no secuirty until they open and close it again. You are also changing settings without asking the user which can be bad but it can deff be done.

 

[Travis]

Yes, you could do that, but you would need to have a development platform (not excel) in which you could create an application to do it. But Id be very leary about developing an app that you are going to release to thousands of coworkers that modifies their registry. I suspect you could get into so heat with that.

 

[Seanzies]

ADVERTISEMENT

Do you know anywhere where there is something like this out there that I could use. I would want to test it on mine first.

 

[bandit_1981]

Travis is correct like i stated it isnt not a safe or smart thing idea to play with their registry but it can be done. You would have to code the application yourself to meet your needs etc. The way I handle this is by placing a sheet at the start of my workbook and hide it on workbook open. If macros are enabled they wont see it if they are disbaled they will. The sheet simple gives instructions with pictures to help show them how to enable macros. I suggest this route.

 

[Seanzies]

ADVERTISEMENT

How have you done that. I think that would be a good route to go and much safer.
I understand what I would need to do to make the instructions page, but how do you determine and know that the macros are disabled. I assumed that you used VB. Do you happen to have the code that you used and wouldn't mind sharing that?

Thanks in advance for all of your help!

 

[Travis]

If macros are disabled then the auto open wont run and the sheet will be visible. If they are enable then the auto open will run and hide the sheet.

Something like this should work.

Private Sub Workbook_Open()
    Sheets("Macro_Security_Instructions").Visible = xlVeryHidden
End Sub

 

[Seanzies]

Travis,
I tried that I created a sheet called Macro_Security_Instructions, did a Format, Sheet, Hide. Then to test, I set my Macro Security to High.
Went to Visual Basic Editor, inserted a module and pasted this code that you gave me.

Private Sub Workbook_Open()
Sheets("Macro_Security_Instructions").Visible = xlVeryHidden
End Sub

Then I saved changes, closed the workbook and reopened and the Macro Security box popped up stating that macros were disabled. Also the Macro_Security_Instructions sheet was not displayed. It was still hidden and the other sheets that should be displaying were visable.
Did I miss something?

 

[Norie]

Seanzies

I think the idea is not to hide the sheet.

The code in the Open event will do that if macros are enabled.