VBA to Limit what Columns a User can View--Question about Security

[ScottyBee]

I have an Excel workbook that has a column for each user, roughly 12 total. When the user opens the workbook, I have code in the Open event that retrieves their username and hides all columns except that of the current user.

When coding this project, I learned that simply opening up the VBE editor in another workbook and typing application.enableEvents = False allows me to open the workbook in question and bypass the code in the Open event. This means that someone who knows a little programming could do the same and view the hours for all the users.

Fortunately, the data shown is not proprietary or sensitive. I was just tasked with a means of creating an "easy" way for each user to see his/her training related hours.

If this was sensitive data, Is there a better method to accomplish this task that does not involve using a web based or SQL Server solution? Thanks for your advice. :)

[mark007]

If the data was truly sensitive then the answer is probably don't do it!

However, you could improve the position by hiding all of the columns initially then adding a worksheet password. Then get your VBA to unprotect the sheet, unhide the relevant column and then protect the sheet (with the password). And don't forget to then add a password to you VBA to stop someone looking at it.

Just be aware that the worksheet passwords are very breakable so this only makes it harder rather than secure.

[ScottyBee]

Thank Mark for the good tip. I will definitely do that. Thanks for your help.