Allow hiding of rows in a locked sheet?

[Thackers]

Hi,
I am preparing a workbook for a client, where I would like to lock it up (so they can't change areas that they shouldn't be changing), but I would like them to be able to hide rows (for example, rows with chart data where there are no values, in order to make the charts look better) - from what I can tell, if I lock a sheet, the user can't hide and unhide rows.
Is there a way around this?

 

[mx45]

Perhaps using the Outline (group) option would work. I used the following code for a worksheet I setup that sounds like what you want.

this worked for me. It should be placed in the thisworkbook module.

Private Sub Workbook_Open()
Dim sh1 as Worksheet
Dim sh as Worksheet
set sh1 = thisWorkbook.Activesheet
for each sh in Thisworkbook.Worksheets
With sh
.Activate
.Protect Password:="private", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Next
sh1.activate
End Sub

--
Regards,
Tom Ogilvy

Credit is due to Tom Ogilvy with Allexperts for the above code.

 

[Thackers]

Okay, I thought you were suggesting that the macro would do everything for me i.e. allow the user to hide/unhide rows/columns as they like while the workbook is locked. But I now see it seems you are saying the macro will just lock the workbook, and I should set it up with outlining (grouping) to allow the user to hide/unhide.

The problem with this is that I want them to be able to hide/unhide rows next to each other(i.e. hide one or the other, but not both at the same time). From what I can tell, I can't group in this way (when I try to, it just combines both rows into one grouping).

Is there a way of "grouping" a row or column just on its own, and also having the neighbouring row also grouped on its own, so they can be hidden/unhidden independantly?

If not, is there another solution to my issue (some code which allows rows and columns to be hidden while a worksheet is locked)?

 

[Jaafar Tribak]

Okay, I thought you were suggesting that the macro would do everything for me i.e. allow the user to hide/unhide rows/columns as they like while the workbook is locked. But I now see it seems you are saying the macro will just lock the workbook, and I should set it up with outlining (grouping) to allow the user to hide/unhide.

The problem with this is that I want them to be able to hide/unhide rows next to each other(i.e. hide one or the other, but not both at the same time). From what I can tell, I can't group in this way (when I try to, it just combines both rows into one grouping).

Is there a way of "grouping" a row or column just on its own, and also having the neighbouring row also grouped on its own, so they can be hidden/unhidden independantly?

If not, is there another solution to my issue (some code which allows rows and columns to be hidden while a worksheet is locked)?

What version of excel fo you have ?
If you have excel2000 or 2003 then you could hook the Hide control to do what you want.

You can follow a similar route with xl2007 by hooking the same control and using a callback routine but that is a bit more involved.

 

[Thackers]

I am using Excel 2007 - unfortunately I am not advanced enough to understand how to execute what you are suggesting. Any more detailed guidance would be great however I understand if its too much trouble.