microsoft excel - Allowing users to unhide (format) only chosen columns

06
2014-04
  • xchru

    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 cant manage to find 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

    • Answers
  • Raystafarian

    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


    • Related Question

    spreadsheet - In Excel, copying cell *values* (not formulas)... yet keeping the cell formats
  • Steve Perkins

    I have an Excel spreadsheet with multiple worksheet tabs for collecting data... and a final tab with a lot of formulas, to summarize the other tabs for a given date range.

    The goal is for the person doing data entry to keep this "master spreadsheet"... and distribute copies of only the summary tab.

    You can't simply cut-n-paste the cells from that tab into a separate spreadsheet (or use "Edit->Move or Copy Sheet")... because that will copy over all the formulas. Those formulas will break when opened on another computer that doesn't have master spreadsheet.

    On the other hand, using "Edit->Paste Special" and selecting only "Values" has problems too. This approach strips away not only the formulas, but also cell formatting (and there is quite a bit of formatting).

    Is there any way to copy over cell values AND cell formatting from one worksheet tab to a separate spreadsheet file? I just want a visually-pleasing snapshot of that summary tab for distributing to other people.


    • Related Answers
  • bot_bot

    Can you not make a template of the destination "summary" sheet and format it the same as the sheets you're taking the data from? so when you paste-special as values your destination sheet is already formatted and you don't need to copy across the formatting.

  • mtone

    5 easy steps:

    1. Copy summary worksheet 'as is' to a new workbook.
      • Right-click worksheet tab
      • Move Or Copy
      • New WorkBook, Create a Copy
      • OK
    2. Select all (CTRL+A)
    3. Copy (CTRL+C)
    4. Paste Values (over itself)
    5. Save and distribute
  • Mike Fitzpatrick

    I need to do this quite frequently. I've found the easiest way is to do a normal paste followed by a Paste Special -> Values in succession. I use the keyboard shortcuts so it is a very quick process.

  • Toc

    In the first sheet:

    1. Copy

    In the other sheet:

    2. Paste
3. PasteSpecial and choose only value.

    Step 2 to copy formulas and format settings. Step 3 to overwrite formulas by values.

  • Diem

    Alternatively, once you've done Paste Special -> Values, do Paste Special -> Formats immediately.

    The destination range should be the data you've just copied over, so as long as you don't move or hit escape (to clear the copied information) you should be fine.

  • Susan

    I like to use an add-in called ASAP UTILITIES http://www.asap-utilities.com/ You can do lots of things with it--and one of the things is to CONVERT ALL FORMULAS TO THEIR VALUES which I find very useful. Doesn't touch the formatting.You might leave the master alone, make a copy of the updated summary, convert formulas to values, and then distribute that page.