Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a workbook with a lot of sheets, macro's and calculations going on. I now need to send this sheet out (not forseen before yesterday). How do I lock it down so that users:

  • are unable to access/see the macros?
  • are unable to unhide hidden sheets?
  • are unable to see code or add code/

I've gone through and unlocked those cells I want them to change. Hidden rows of buttons and formulas and then locked them. Hidden worksheets. I've now protected the worksheet with a password.

However, does this stop users from accessing the Macro's/Code? or unhiding sheets? If not, how can do I lock the workbook down correctly/securely/totally?

It sounds like a big-brother type of security protection, but I'm just making sure that all the interlinked cells are not accidently changed, or a macro is run that I don't need them to run.

Many thanks

Michael.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
+1 for typing in what I was typing :D –  subanki Sep 13 '10 at 10:18
    
Thanks for the detail. I've managed to lock it down based on your answers (and the link from Travelling tech guy below). –  RocketGoal Sep 13 '10 at 10:21
    
The xlVeryHidden doesn't seem to work. Not sure why. I've made a module and put the code in that, and then tried actually putting the code in the sheet itself. With both cases I could still right- click on the tab/s at the bottom and the menu still showed the VeryHidden sheets. Strange. –  RocketGoal Sep 14 '10 at 8:25
    
@Mike have you run the code with the very hidden trigger in? –  DMA57361 Sep 14 '10 at 9:07
    
Good question. No I think I just saved it. So, that's where I went wrong. Oops. –  RocketGoal Sep 14 '10 at 9:44
show 1 more comment

Try this site - it seems to be answering all 3 questions.

share|improve this answer
add comment

If you only want recipients not to change some bits accidentally, Excel's protection will suffice. But if there is confidential data that you don't want people receiving the workbook to be able to see, they're no good.

If you only want to show the data and not the formatting, a simple way of “flushing” all hidden data is to export each sheet as CSV, then reimport them as a new Excel workbook. (The second step is strictly speaking optional, but people are not used to CSV files, so it's better to send an Excel file like they expect.)

If you want to have presentable output that people can't modify, print the sheets to a PDF file and send that.

share|improve this answer
    
The users need to use this sheet, unfortunately, but with the protection advice above I'm happy that it's protected to a standard'I and my colleagues are happy with. It's not confidential information, just a lot of time in writing code, and formatting that some of the users may be interested in looking at. –  RocketGoal Sep 14 '10 at 8:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.