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 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!

share|improve this question
    
if you can keep that data on other sheets, you can use .visible=xlVeryHidden to hide the sheet from being unhidden from the right click menu –  Sean Cheshire Jun 8 '12 at 15:21

1 Answer 1

@Sean has mentioned putting the hidden data on a different sheet which was my first thought too.

My second thought was to have an auto-open macro that checks the current user and hides the columns then turns on protection. Not the most secure since the VBA code will need the password but it does get round the other issues. You can also protect the code which will reduce the issue of embedding the password.

In the past, when I had a complex workbook with parts that needed to be hidden, I had some macro's in my personal.xls that helped lock/unlock and hide/unhide the appropriate parts.

share|improve this answer

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.