1

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!

Improve this question
2
  • 1
    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
    – SeanC
    Jun 8, 2012 at 15:21
  • Does the data in the first 3 columns still need to be there when it's hidden? Would it work if those columns were empty except for certain users? You could store the data in a xlVeryHidden sheet (or even another file entirely) and bring it over as needed.
    – Engineer Toast
    Apr 20, 2022 at 13:28

2 Answers 2

Reset to default
0

@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.

Improve this answer
0

There are already some workarounds proposed for you in previous post / comment, however you need to know that all those can be easily avoided with some medium VBA knowledge.

If you really work with sensitive data, then Excel is probably not the right tool for you.

As I don't know your full data, it's hard to suggest anything, however based on what you've shared, SharePoint list seems to be a good candidate.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .