Locked grouped rows problem

bhavesh78 · #1 (**permalink**) 01-05-05, 13:33

Hi,

I have a sheet that is locked (by macro) and distributed to users. Some of the rows in the sheet are grouped together. The problem is that, if the sheet is locked, the user cannot expand/contract the grouped rows.

Is there a way using macro/VB code so that user can expand/contract the grouped rows while the sheet remains locked.

Thanks.

Bhavesh

shades · #2 (**permalink**) 01-05-05, 13:42

What version of XL are you using? With XL 2002 or 2003, you have more options to allow users to make changes.

Yes, you can code to unlock and relock. However, maybe a better way is to allow UserInterfaceOnly, and control all options within code (including the grouping options). This prevents changes by the user, but allows code to manipulate the worksheet.

bhavesh78 · #3 (**permalink**) 01-05-05, 15:59

I am using XL2002 on Win2000 platform.
Can you please elaborate on the code part. What I need is-- to let user expand/contract rows on a locked worksheet.

Thank you.

Bhavesh.

shades · #4 (**permalink**) 01-05-05, 16:50

It may depend on how complicated you want the choices.

Code:

Sub ProtectAndAllowGrouping() 

   Dim strPassword As String 

   strPassword = "mypassword" 

   ActiveSheet.EnableOutlining = True 
   ActiveSheet.Protect Password:=strPassword, userinterfaceonly:=True 

End Sub

This protects the active sheet with password "mypassword" but still allows user to expand and contract groups. Problem: settings are not saved once you close and re-open the workbook.

Possible solution: call the macro automatically each time the workbook opens [code goes in Workbook code module]:

Code:

Private Sub Workbook_Open() 
   ProtectAndAllowGrouping 
End Sub

(To open the workbook code module double-click the ThisWorkbook icon in the VBE Project Explorer. Or, right-click the workbook's icon in Excel and choose View Code.)

bhavesh78 · #5 (**permalink**) 01-05-05, 18:36

Cool. The code works great. Thanks Shades.

My other problem now is that I am unlocking only one row in the macro. This one row is replaced by multiple rows fetched from database and grouped together in one row when another macro is triggered.

Now since I have unlocked only one row, I am able to edit only the first row of the grouped rows. Is there a way to unlock all the rows that are grouped together. I cannot hard code the values in macro as the rows are fetched from database and the number of rows is unknown.

Thanks again for all your time and effort.

~BS

Internet Services

Web Development

Digital Marketing

Consumer Tech