5

TL;DR, what I want to achieve:

  • Hide some columns which I do not want to show to anyone (check)
  • Hide some columns which I want to show only to some users (check)
  • Set separate passwords for both column groups (one I would not disclose, and the other I would disclose to selected users) (uncheck)

But the problem is that:

  • If I check "Allow all users to format columns" on level of protecting whole sheet, ALL columns may be unhidden by anyone
  • If I set password for range ("allow users for editing ranges"), while "allow all users to format columns" is unchecked, no column can be unhidden without knowing the superior password (which protects also the columns which I do not want to show to anyone)

Full story:

I have an excel file which I would like to share. There are certain hidden columns, which I do not want anyone to unhide. But on the other hand there are 2 columns which I would like to be protected in a way that some users are able to view (unhide) them.

I have dug into protecting sheets, giving users access to ranges, etc, but I can't manage to find a solution to column hiding/unhiding.

I tried hiding all of the columns (the ones that I want perma-hidden and the ones that I want to be unhidden by some users) and setting a password for the sheet editing. Then, I figured that I would set a separate password for editing ranges (and I selected the columns I wanted). But still, its not possible to unhide the separate range (consisting of columns I want to show to some users by giving them password to this particular range).

So I tried implementing something on the level of whole sheet and found out that there is attribute "Allow all users.." -> "Formatting columns". But then, all of the users will be able to unhide ALL of the columns (and I only want them to be able to unhide columns I choose).

BTW, I use Excel 2007.

1
  • 1
    I'd suggest moving those columns you want to hide from everyone to a separate sheet which you protect and hide. You can easily reference cells in a different sheet if necessary.
    – Daniel
    Jan 15 '14 at 14:35
2

I'd place the hidden cells on another sheet and protect it via VBA such as:

Sheets("sheet4").Visible = xlVeryHidden or

Worksheets("sheet1").protect "Password", UserInterfaceOnly:=True or

ActiveSheet.protect "Password", AllowFormattingRows:=True, AllowFormattingColumns:=True

Or other methods. xlveryhidden is a good place to start, then lock the UI and access to VBA

0

I had a similar issue and used data grouping to separate grouped rows (which I allowed the user to expand) and hidden rows (which could not be unhidden). Protecting a sheet without allowing user formatting results in hidden rows which cannot be unhidden. The expansion of column groupings was allowed through the following code:

Dim wSheet as Worksheet

For Each wSheet In ThisWorkbook.Worksheets
      wSheet.Protect Password:="Password", UserInterFaceOnly:=True
      wSheet.EnableOutlining = True
Next

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.