Making a workbook a trusted document- Always. Always enable macros

[emilyloz]

Hi,

I have a workbook containing macros, and it's being shared in a location accessible to multiple users.
I want to have the content in the workbook always enabled. and always a trusted location

I do not want a user to have to select 'Enable Content' or 'yes-make trusted file'

I know its 'highly not recommended' but, it is a must.
Some of the users didn't enable everything, ran a bunch of stuff on it, saved it, and it deleted a bunch of content on my file, leaving me with a huge mess to clean up after.

I've read a few articles about it, but i'm not sure which option is right for me.

Please Help

[TMS]

The usual approach is to make the workbook unusable unless macros are enabled. You do this by having a Splash screen (worksheet) that advises the user(s) that they need to enable macros. All other worksheet will be hidden (set to xlVeryHidden) in the VBA code whenever the workbook is saved and before it is closed.

Ideally, protect all the sheets with a password and the workbook structure. And you will need to lock the VBA Project for viewing.

It's messy but doable. You should be able to find examples if you Google it.


Regards, TMS

[emilyloz]

Okay, I will research splash screens.

What i'm seeing so far is that the splash screen will give instructions to the user on how to enable macros.

Isn't there a security thing for the file that I can save it as, 'always enable macros' or something of that sort?

My users are very unfamiliar with excel, so I just want this to be done for them. anyway?

[Solus Rankin]

What OS are you running? Is it the same for all computers?

[emilyloz]

Windows 7
Microsoft 2010.
yes, it should be the same. Some *might* have vista or xp, but we all have Microsoft 2010.

[Solus Rankin]

If your running enterprise, the easiest way would be to contact your LAN server group to change your group policy.

Then a simple .bat file could check and make sure they had the most recent policy before trying to open the macro.

[emilyloz]

You just spoke very intense gibberish to me. Im quite lost with that last post.

[shg]

You could buy a code-signing certificate from VeriSign or other provider, and sign the project. The first time the workbook opened, users would have the option of clicking 'always trust this publisher,' and thereafter macros would run without prompts.

[emilyloz]

Buying anything is not an option, i'm simply an intern, and don't even know how often and even if my program will be used.

I have these options checked:
screen.jpg

Can someone address the question: "Is there a way to always 'Enable Content?"

[Solus Rankin]

Sorry for the confusing post. Your LAN server group is the team that updates and maintains your company's server. Group policies are rules they set up so different users have different rules (established every time a user signs in. They can update the group policy to change Excel settings (like change trust center settings to enable macros) for entire groups of people.

[shg]

That's a setting every user would have to make on their computer, or the IT person would have to make it for them. In the latter case, the IT person should be taken out and shot.

Do you appreciate how much damage a malicious macro can do?

[emilyloz]

That's a setting every user would have to make on their computer, or the IT person would have to make it for them. In the latter case, the IT person should be taken out and shot.

Do you appreciate how much damage a malicious macro can do?

Yes, I already spoke to the IT guy, and he said with the settings and security already on our computers, it would be find to always enable these macros.

[shg]

it would be fin[e] to always enable these macros.

The setting isn't selective; it would always enable ALL macros. He's apparently the first guy that needs to be shot.

[Solus Rankin]

The IT person can dictate in the group policy that only macro enable workbooks from a source already inside the server are opened as enabled.

[emilyloz]

The setting isn't selective; it would always enable ALL macros. He's apparently the first guy that needs to be shot.

Okay, please don't be rude.

Are you saying that by enabling macros for that workbook would enable macros for every workbook from here on out?

[Solus Rankin]

Yes. They will be enabled for all workbooks saved on your server (or local machine) from here on out.

shg is right it does leave a security loop hole. But if the LAN server group sets it up right it will be obscure, and I'm fairly certain MillSteel is not a branch of the DoD.

[shg]

Yes, it's an application setting, not a workbook setting.

If that were not true, it would be useless. People would just send out malicious macros with 'trust me' set.

[emilyloz]

Okay, well I changed all those settings back to the default. But now i'm just lost on what to do.

[shg]

To keep users from modifying the file, make it read-only on the network share.

Then explain to them how to enable macros. People are surprisingly trainable.

Or talk to your IT guy about xero's suggestion. I have no clue about how IT policy management works.

[emilyloz]

Well, unfortunately if these people are anything like my mother- they are surprisingly untrainable.

thanks for the help.

[shg]

I'm going to tell your mom you said that ...

[Solus Rankin]

This is how it would look if your LAN server group wanted to set the macro settings for the group policy based on source:

Capture 2 shows my macro settings in trust center

Capture shows the enable warning at top when I attempt to open a macro enable worksheet from a source outside our company's server.

Capture.PNGCapture2.PNG