First - note that most of the protection features in Excel are not security features. Most are quite easily broken or bypassed. They are fine to stop accidental damage - but will not stop anyone who knows what they are doing and really wants to see bits you've hidden.
That said, here's some answers:
Inital Protection
You've covered this, basically lock cells and hide things and needed.
Then protect every sheet.
Then protect the workbook itself.
Very Hidden Sheets
Sheets can to be made "very" hidden, this means then an only be unhidden via VBA (they don't appear in the unhide list in the interface). This is simply done via a line of VBA, which needs to be run at least once to trigger the change of setting, and doesn't need to be kept in the file once run (but this might be useful to remember which sheets to unhide later):
Sheets("Sheet1").Visible = xlVeryHidden
Hidden VBA Code
Set a password on your project. From the VBA editor right-click your module in the Project Explorer
, choose <ProjectName> Properties
and set a password on the Protection
tab.
Note that these can be brute-forced open.
Hidden Macros
I assume here you mean you don't want them to appear in the macros list when someone opens the Run Macro
window?
To do this make the desired macro either a Private Sub
or a Function
, this makes them non-visible as macros but still allows them to be called by other code in the same module.
I don't think you can't prevent people from running the visible macros and you cannot assign hidden macros to buttons.
Security Update (Dec 14) from MS is definitely the issue.
Once someone with the update saves a file, no one else can open it without issue- until they are updated- which is why it appears hit or miss... (Issue kills Active X controls.)
Office 2007
get current on all MS updates and restart
update to SP3 if necessary (not enough points to post link)
install Security update Security Update
run FixIt 51029 patch to remove exd files FixIt 51029
Office 2010/2013
get current on all MS updates and restart
run FixIt 51029 patch to remove exd files (same as above)
And yes, on all the computers...
Best Answer
You can do this either by editing the Office installer, or by importing the Office Administrative Template and then editing your office setting through group policy.
As editing the office installer worked for me to add the path, but not to enable "Allow Trusted Locations not on this computer, I will explain how to do this both ways.
To configure Office settings using the Office Customization Tool.
setup.exe /admin
(The OCT is available only with volume licensed versions of the 2007 Office system. Office Standard 2007, Office Small Business 2007, Office Professional Plus 2007, and Office Enterprise 2007)Allow trusted locations that are not on users computer
If you are like me and step 3 doesn't work to allow non-local locations, you can also set the setting through local (or global) Group policy
User Configuration/Administrative Templates/Microsoft Office Excel 2007/Excel Options/Security/Trust Center/Trusted Locations
Enabled
This allowed us to skip explaining how to enable macro's dozens of times. With only slight modifications, it will also work for Word, Access and the rest of the programs in the office suite.