I have a shared Excel file that I need multiple people to work from at once. The file contains some macros that hide certain columns based on the username.
- 1) check for username
- 2) hide columns A, B, C (for specific users)
Once the columns are hidden for a "restricted" user, I don't want the user to be able to unhide the columns by just right mouse clicking and selecting "unhide". I can't really protect/unprotect the sheet using the macro (-> since it's a shared file. it's throwing an error 1004, I believe). To protect the sheet via Excel toolbars won't work either (hiding / unhiding sheets in macros doesn't work on protected sheets).
To summarize:
Shared file -> unprotected sheet (prb: users can unhide hidden columns)
Shared file -> protected sheet (prb: can't hide columns using macro)
So I am thinking, I don't need the sheet protected. I just want to make sure that once the columns are hidden that the user won't be able to unhide them. Any ideas how to do that?
Perhaps, disable user control or something. I am not very familiar with vba.
Any tips or suggestions would be appreciated.
Thank you!
.visible=xlVeryHidden
to hide the sheet from being unhidden from the right click menu