Protect problem with expanding hidden rows

[appo]

I created a sheet with a lot of rows which I hide/unhide using SHIFT-ALT-RIGHT ARROW function.

When I lock certain cells and after that protect the sheet it is not possible to expand the hidden cells anymore.

Who knows if there is a solution to this problem ?

regards, Anne ----------------------------------------
A. Postma
jr. ICT specialist
Logica BV
----------------------------------------

 

[DaleWatson123321]

Hi Anne,

As you discovered, once a worksheet is protected, one cannot expand hidden cells. This should be the case, as protection of a sheet SHOULD protect against unhiding all such hidden rows/columns/cells etc.

There is, however, a solution for your situation. It involves using VBA code to hide/unhide the ranges - prior to and after performing the rest of the process you are performing. These are the steps you should follow:

1) Eliminate the "manual" method of hiding/unhiding rows (the SHIFT-ALT-RIGHT ARROW function). Instead, use VBA code - example to follow...

2) Create a Range Name for each of the ranges (of rows) (or could include columns) you want to hide and unhide. You only have to highlight one column - example A15:A36 - then use Control - F3, and type in your (arbitrary) range name.

3) Password Protect the Sheet, by using Tools - Protection - Protect Sheet. Naturally, remember (write down) the password.

4) Use subroutines such as the following, to unhide and hide the ranges: (And, if you are hiding these ranges in connection with some other process such as printing, you probably also want to include VBA code for printing - to make the entire process "complete".) Once you complete your entire routine(s), you probably should attach the routine(s) to "button(s)".

Sub Hide_Rows() 'hides rows identified by range names
' Keyboard Shortcut: Ctrl+Shift+A
ActiveSheet.UnProtect 123456 'unprotects sheet
Range("range1&quot.EntireRow.Select 'selects "range1"
Selection.EntireRow.Hidden = True 'hides the range
Range("range2&quot.EntireRow.Select 'selects "range2"
Selection.EntireRow.Hidden = True 'hides the range
ActiveSheet.Protect 123456 'protects the sheet
End Sub

Sub UnHide_Rows() 'unhides rows identified by range names
' Keyboard Shortcut: Ctrl+Shift+S
ActiveSheet.UnProtect 123456 'unprotects the sheet
Range("range1&quot.EntireRow.Select 'selects "range1"
Selection.EntireRow.Hidden = False 'unhides the range
Range("range2&quot.EntireRow.Select 'selects "range2"
Selection.EntireRow.Hidden = False 'unhides the range
ActiveSheet.Protect 123456 'protects the sheet
End Sub

5) Naturally, if you are protecting the sheet, you likely don't want the user to be able to view your VBA code and gain access to your password. Therefore, use the following to password protect your code:

5a) Use the &quot;Visual Basic Editor&quot; - <Alt> F11.

5b) Use Tools - VBAProject Properties - Protection.

5c) Check off &quot;Lock project for viewing.

5d) Enter a password.

Note: After Step #5, you will have to save the file, and retrieve it, in order for this protection to &quot;take effect&quot;. The result will be that when the user attempts to view the code, all he/she will have access to is &quot;Run&quot; and &quot;Cancel&quot;. All other options will be &quot;grayed out&quot;.

IMPORTANT REMINDER: Make a hard-copy of your password(s), and keep them in a safe place. You should probably also share these passwords with a co-worker (your boss) - in case of a &quot;worse case scenario&quot;. Recently, a Tek-Tips member posted a plea for help in gaining access to several password protected files which had been (unknowingly) protected by a worker who was killed in an accident.

If you don't follow all of the above, I could email you a copy of the example file I created. Just email me, and I'll send it by return email. This also applies to any other Tek-Tips users.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca