Excel: Security - no VBA - Hide columns and protect....

[jasonlogical]

Hello All,

I have a tricky situation. I hope there is a solution for it. Otherwise I will have to rework my workbook.

I have a worksheet that has public information in columns A through R. It has confidential information in columns S through AD. I need to share this spreadsheet on a regular basis with departments that cannot see S through AD.

Here is what I want:

I want the ability to edit columns A through R without unprotecting the worksheet.
I want the ability to sort using autofilter without unprotecting the worksheet.
I want columns S through AD to be hidden or invisible to the user with no hope of seeing the data without a password.
I don't want columns S through AD to be edited or copied without a password.

Is there a way to do this without VBA?

Thank you very much for your time.

 

[Rhodie72]

Yes, there is, but it means doing it manually every time.However, if you decide that manual is too much to ask for and you can use VBA in your personal macro workbook, then it would be far less tedious.

What you'd have to do without VBA is hide the colums and then protect the workbook from hiding or adjusting the hidden columns with a password. This would mean that all the visible cells should be unprotected initially.

Problem solved. With VBA you'd only have to press Ctrl+F8 and run the relevant macro on your machine. Job done. It would alternately hide/unhide the sensitive data columns

 

[FDibbins]

ex cell's security is notoriously bad...

I want columns S through AD to be hidden or invisible to the user with no hope of seeing the data without a password.

Anyone with some knowledge will be able to get past it.

Do you want the users to be able to change/add/delete certain stuff and then save the file, or just mess with it, and not be able to save?

1 option to consider would be use the Save As function with password protection set to Read Only.
Another option might be to put the sensitive data in it's own file, and then reference it - that way, it is not even in the shared file

 

[Rhodie72]

Excel's security is notoriously bad...

Anyone with some knowledge will be able to get past it.
...
Another option might be to put the sensitive data in it's own file, and then reference it - that way, it is not even in the shared file

He has a strong case there. I cracked the security Microsoft put on their add-ins with ease. The best way to secure sensitive data is to not release it. Imagine if this sensitive data fell into the hands of somebody"reasonably competent" like myself , would the legal fallout be worth the belief that the users are too ignorant to access the data? Excel security has readily available tools online to crack, bypass or even replace the intended security of the file itself.

the safest way is to secure the data in a separate workbook with the operating system, and have the data encrypted so that only the correct user permissions allow electronic access to the file, but the encrypted data is only accessible via the secure password to the actual hardware data. PGP was good with security. Now it's owned by Symantec

 

[jasonlogical]

Hi all,

Sorry for the late reply. Thanks very much. I will use separate workbooks. I work hard to be lazy, but sometimes it just doesn't work.

Thank you