Locking Excel worksheets from view?

[MickG]

I need to lock certain worksheets in an Excel file from view and send to various branches of our business (with certain sheets locked for the respective branches) How do I do this?
Essentially I have 3 branches where figures are collated and I only want each branch to see ther own figures and not the other branches, but I still need all the worksheets to sit on the one file for graphing/calculating purposes etc.

Have found how to protect but thaat just password protects the sheets so they can't modify, but they can still view.

Any help appreciated, thank you.

Aye, Mick

[dmdigital]

Not sure if it can be done. You can hide a sheet (but it can be unhidden) or lock cells or the whole sheet from editing.

What I'd suggest is have 3 workbooks with a single sheet in each one for each group and have a 4th one which links to these three for you charting etc.

[MickG]

Not sure if it can be done. You can hide a sheet (but it can be unhidden) or lock cells or the whole sheet from editing.

What I'd suggest is have 3 workbooks with a single sheet in each one for each group and have a 4th one which links to these three for you charting etc.

thanks dm_td5, that was my other thought although thaat would mean 3 separate fiels and then there is the problem of linking those onto 1 single file........how is this done?

Ideally i would like 1 data entry point for each branch and the information to automatically collate for the group..........hoping i am not asking too much of Excel.

thanks again, Mick

[awabbit6]

Are you planning to send the same file from one branch to the next with each one inputting data before forwarding?
If not, then dm_td5's suggestion is probably best. If you would rather not link the files, you can copy worksheets and collate them into one workbook in which you do your calculations.
What you want to do is possible if you use Visual Basic for Applications (VBA). On opening the sheet a window can be made to appear into which the user enters their credentials. Based on the result, you can then have the spreadsheet hide/lock appropriate sheets. Not the 'click an option' solution that you were probably looking for but it would certainly work.

Once you get into VBA you'll realize that there's alot more to Excel (and all Office apps for that matter!)

[dmdigital]

thanks dm_td5, that was my other thought although thaat would mean 3 separate fiels and then there is the problem of linking those onto 1 single file........how is this done?

Ideally i would like 1 data entry point for each branch and the information to automatically collate for the group..........hoping i am not asking too much of Excel.

thanks again, Mick

You can link sheets from other workbooks into one using formula's in the cells. Best thing is to read up on linking spreadsheets in the help, very straight forward.

The VBA option is also quiet viable but if you haven't programmed before I would recommend the linking option

[incisor]

nothing in excel is secure in any way shape or form..

there are freely available tools that cost nothing that will get you into anything in office...

[stikman]

Hey Mick,

As Inc has said there is probably easy ways for people to unlock or get past any of the security but it is easy to hide and password protect a sheet within the file. Have the sheet you want to hide open, select Format>Sheet>Hide. Once hidden select Tools>Protection>Protect Workbook and put in a password. Once the workbook is protected the unhide sheet option is not available. Inc would be more of an expert on breaking this protection but if you dont think the people you are sending it too are going to go to the effort of getting around this then the simple option you requested is available. Give me a call anytime ;)

[dmdigital]

nothing in excel is secure in any way shape or form..

there are freely available tools that cost nothing that will get you into anything in office...

Check out the encyption levels of password protection offered post MSOffice 97. Yes there are tools that will brute force their way to the passwords, but they can take months. One of the biggest issues I've had is with people password protecting their documents and spreadsheets and forgetting what it was:mad: Access database passwords are, on the other hand, very unsecure!

[MickG]

Thanks guys

No real security issues as I am only sending the files to some of my staff who at the end of the day would not be that interested in seeing the other sheets, but if they did, no great loss......although would still like to hide. Very sure they would not have the know how or patience to hack the passwords.

Also trying to expand my knowledge of Excel through this process as I use it everyday now and realise I only use a fraction of it's capabilities.

Also as pointed out, there is already a fundamental flaw in what I am trying to do - which is have one data entry point for each branch - this would not work as I would have to pass the file round each office for all to update or set it up on a server type set up, but then that's a whole other kettle of fish.

Anyway, have managed to figure out how to hide a sheet - cheers stikman;) - and tomorrow is set down for a bit of light Excel reading from the Microsoft help section.

Appreciate everyone's input once again as I fumble through some IT essentials.

Aye, Mick

[jik22]

Check out the encyption levels of password protection offered post MSOffice 97. Yes there are tools that will brute force their way to the passwords, but they can take months.

Even with good passwords, modern tools will unlock most Office apps in seconds.....

[incisor]

Even with good passwords, modern tools will unlock most Office apps in seconds.....
yep.. do it all the time...

milliseconds on a quad mac :P

and usually under 2 minutes on an imate jamin

[LoveMyV8County]

Go to Tools -> Macro - Visual Basic Editor

About half way down on the lhs there should be a Properties window that shows various attributes of the current worksheet. The last one of these is "Visible", that can be changed to "Hidden" or "VeryHidden".

Not entirely secure but much more difficult for a casual user to discover than the usual Format -> Sheet -> Hide.

Chris

PS My understanding from past research is that passwords for recent Office docs such as Excel can only be broken using brute force so using cryptic (non-dictionary) passwords mixing upper/lower case, numbers and symbols will delay cracking for some time. Just my 2c

[incisor]

PS My understanding from past research is that passwords for recent Office docs such as Excel can only be broken using brute force so using cryptic (non-dictionary) passwords mixing upper/lower case, numbers and symbols will delay cracking for some time. Just my 2c
i suggest you have a wee look around a few of the lEEt sites, your perception will alter fairly quickly...

[MickG]

nothing in excel is secure in any way shape or form..

there are freely available tools that cost nothing that will get you into anything in office...

Where might one find these tools and go about breaking password protected files?

[incisor]

dogpile is your friend...

a few searches and you will have all the info you need..

eg the search string "office password removal" gets you a bucket load of useful info to sift thru...

ALWAYS virus scan anything you download before using it, some of these things are scams.. :P

[MickG]

dogpile is your friend...

a few searches and you will have all the info you need..

eg the search string "office password removal" gets you a bucket load of useful info to sift thru...

ALWAYS virus scan anything you download before using it, some of these things are scams.. :P

You are a clever man Inc:) Very much appreciated and i tell you, it's faster using this resource than it is talking to the IT guy I sometimes use:D

Thanks again and great work, Mick

[jik22]

Very much appreciated and i tell you, it's faster using this resource than it is talking to the IT guy I sometimes use:D


Then you need a new IT guy - especially if he thinks Office passwords are secure!! ;)

BTW, if anyone else is wondering, the same goes for all your Windows, mobile device and Linux passwords too - if you have physical access to the machine, you can get in as quickly as it takes to reboot. Only high grade encryption of the actual disk contents is worth anything security-wise.