Excel 2010 - Lock out Trust Center settings for Disabling All Macros?

Hello,

There are some excel documents in a hidden folder that contain sensitive information. Shortcuts to these documents will be provided to users to open them and add data. These documents contain all kinds of macros as well. Once macro disables the 'Save As' feature so that the workbook can not be saved outside of the hidden folder, preventing to some degree a user from saving as a different name and emailing the document with the sensitive data.

This all works fine with Macros enabled. As soon as macros are disabled the 'Save As' prevention is bypassed and the workbook can be saved anywhere.

Is there a way for an administrator to lock out the Trust Center options for disabling all macros?

Thanks for any help.

TV

Hi Steve,

That's a pretty simple solution. So I guess you are using the xlVeryHidden function to hide all sheets except the one with the message to turn on macros, everytime the workbook is closed. So if macros turned on, the Open_Workbook() macro will take care of showing all the hidden sheets and hiding the one with the message to turn on macros.

I like it, I will give it a try.

Thank you for your input.

As for the method to use the shortcut to prevent copies of the document leaving the premises, I knew it's not ideal and there are holes in it. I did not consider the file path as you mentioned so thanks for the heads up. As well, someone can just copy and past a sheet into another document. It's probably impossible to prevent any type of data leakage.

What approach if any might be commonly used to prevent data leaving the building when using Excel?

TV

Hi TV,

No I'm using "Sheets("Sheet1").Visible = False" & Sheets("Sheet1").Visible = True. This is fine if you protect the workbook as well as the worksheets as a user cant unhide a hiden worksheet unless they have the password.

Regarding copy and paste:
If you protect the worksheets, in the protect worksheet options you can stop anyone selecting protected cells so they cant copy them. I use this method and populate the worksheets with a userform which unlocks worksheets with code, pastes data in, then locks worksheets again. However even with this they could still just do a screen print!

Another option you may consider is populating the workbooks with sensitive data by using an external workbook that populates the sensitive workbook using some code. This way they would never see the sensitive data in the master workbook.

Regards
Steve

Steve, thanks so much for sharing your code examples. I have password protected sheets with locked cells and this should be pretty easy to implement.

I don't quite understand why Microsoft does not allow for an admin to control the settings for enabling and disabling macros, especially since malicious code can be run. Would it not make sense to have that functionality? A user would not be able to turn off macros, disabling the functionality of workbooks and they would not be able to enable 'all macros' which could end up running malicious code.

Cheers,

TV

Hi TV,

Appears you may be able to get what you want, quote below from Microsoft website:

You can change macro security settings in the Trust Center, unless a system administrator in your organization has changed the default settings to prevent you from changing the settings.

HTML Code: 

The only thing is, I cant find how you do this!

Regards
Steve

Hi,

Looks like you may need to take this to another forum (not excel forum), although not sure which one. Or if youre lucky enough to have one, a good IT department ?

Quote below is again from Microsoft website:

Some users may not be able to change Trust Center settings due to group security policies in their organizations. In such cases, you need to contact the IT administrator for your organization.

Regards
Steve

Hi,

This is all beyond my knowledge but it may help set you on your way.

HTML Code: 

Please let me know how you get on, I would be very interested if you did succeed.

Regards
Steve

Thank you very much Steve,

HTML Code: 

We have an IT guy that comes in from time to time, maybe he will know more about OCT or maybe someone else here on these forums may know.

Cheers,

TV