• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hidden Columns Password protected

D

DanniJ

New Member
I have a worksheet that I want to share with various people. Some of the information is confidential and I want to hide those columns and password protected those columns. Ultimately I would like to have a macro that will hide and unhide with a password.

Thanks
Dannij
 
Good day and welcome to the forum
Excel was never designed for multi users, for that you should be importing your Excel into Access, because of this security in Excel borders on the useless were as in Access you can lock down your data and what the end users can and cannot see/do.

How ever you could do the following.

  1. Select the column you want to protect.
  2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
  3. Make sure the Protection tab is displayed. (See Figure 1.)
    T8069F1.jpg


    Figure 1. The Protection tab of the Format Cells dialog box.
  4. Make sure both the Locked and Hidden check boxes are selected. (It is the Hidden check box that controls whether the cell contents are visible in the formula bar or not.)
  5. Click OK to dismiss the dialog box.
  6. With the column still selected, display the Home tab of the ribbon.
  7. Starting in the Cells group, click Format | Hide & Unhide | Hide Columns. Excel hides the column.
  8. Again starting in the Cells group, click Format | Protect Sheet. Excel displays the Protect Sheet dialog box. (See Figure 2.)
    T8069F2.jpg


    Figure 2. The Protect Sheet dialog box.
  9. Enter a password to be used in protecting the worksheet.
  10. Using the check boxes, select what type of protection you want applied to the worksheet. At a minimum you should choose Select Locked Cells and Select Unlocked Cells. (These two options are selected by default in a worksheet.)
  11. Click OK to dismiss the dialog box. You are prompted to reenter your password (the one from step 9), which you should do.
At this point someone cannot view what is in the hidden column, even if they use F5 to jump to one of the cells in the column; it still won't appear in the formula bar. There is one caveat to all this: If you have some cells in the worksheet (or workbook) that are unlocked, so that the contents of the cell can be changed, it is still possible to see what is in individual cells of the column. How? Two methods, really:
  • In the unlocked cell, enter a formula that references a locked and hidden cell. For instance, if column E is locked and hidden, entering the formula =E3 will display, in the unlocked cell, the value in cell E3.
  • Copy the contents of the locked and hidden cell and paste it in the unlocked cell. Use F5 to jump to the locked and hidden cell, then press Ctrl+C, move to the unlocked cell, and press Ctrl+V. The contents of the locked and hidden cell are pasted in the unlocked cell.
The bottom line is that it is virtually impossible to 100% protect the contents of the column so that they cannot be viewed. Using the protection features of Excel makes it more difficult, but a determined user may be able to still view the contents in the described manner.


.
 
Hi Bob ,

1. If a cell is locked and hidden , and the worksheet is protected , I find it impossible to Go To that cell , using F5 or CTRL G.

Is this possible ?

2. Even if an unlocked cell is used to reference a locked and hidden cell , it will only display what would have been displayed in the locked and hidden cell if it were not hidden.

It does not tell the user anything about how that displayed value has been derived ; it may be a user-entered value , a value arrived at using a formula , or a value arrived at using a cell format , either Custom or Conditional Formatted.

If enough important cells are locked and hidden , it may well be impossible to trace dependencies and arrive at how the worksheet is doing what it does.

Narayan
 
Good day @NARAYANK991, apologizes for the delay in replying but been busy having a fight with the weeds in the garden (weeds 1 Bob 0). I did not realise that the OP wanted to know how the value is derived, just to hide some columns.
But I really do believe it is all a bit academic as any security measure taken (short of VBA ) in Excel is only tissue paper over cracks.
 
bobhc said:
Good day and welcome to the forum
Excel was never designed for multi users, for that you should be importing your Excel into Access, because of this security in Excel borders on the useless were as in Access you can lock down your data and what the end users can and cannot see/do.

How ever you could do the following.

  1. Select the column you want to protect.
  2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
  3. Make sure the Protection tab is displayed. (See Figure 1.)
    T8069F1.jpg


    Figure 1. The Protection tab of the Format Cells dialog box.
  4. Make sure both the Locked and Hidden check boxes are selected. (It is the Hidden check box that controls whether the cell contents are visible in the formula bar or not.)
  5. Click OK to dismiss the dialog box.
  6. With the column still selected, display the Home tab of the ribbon.
  7. Starting in the Cells group, click Format | Hide & Unhide | Hide Columns. Excel hides the column.
  8. Again starting in the Cells group, click Format | Protect Sheet. Excel displays the Protect Sheet dialog box. (See Figure 2.)
    T8069F2.jpg


    Figure 2. The Protect Sheet dialog box.
  9. Enter a password to be used in protecting the worksheet.
  10. Using the check boxes, select what type of protection you want applied to the worksheet. At a minimum you should choose Select Locked Cells and Select Unlocked Cells. (These two options are selected by default in a worksheet.)
  11. Click OK to dismiss the dialog box. You are prompted to reenter your password (the one from step 9), which you should do.
At this point someone cannot view what is in the hidden column, even if they use F5 to jump to one of the cells in the column; it still won't appear in the formula bar. There is one caveat to all this: If you have some cells in the worksheet (or workbook) that are unlocked, so that the contents of the cell can be changed, it is still possible to see what is in individual cells of the column. How? Two methods, really:
  • In the unlocked cell, enter a formula that references a locked and hidden cell. For instance, if column E is locked and hidden, entering the formula =E3 will display, in the unlocked cell, the value in cell E3.
  • Copy the contents of the locked and hidden cell and paste it in the unlocked cell. Use F5 to jump to the locked and hidden cell, then press Ctrl+C, move to the unlocked cell, and press Ctrl+V. The contents of the locked and hidden cell are pasted in the unlocked cell.
The bottom line is that it is virtually impossible to 100% protect the contents of the column so that they cannot be viewed. Using the protection features of Excel makes it more difficult, but a determined user may be able to still view the contents in the described manner.


.
Click to expand...
when you use this feature you cannot run macro, right? or did you have an idea to run macro with that feature of excel?
 
You must log in or register to reply here.
Back
Top