Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Locked grouped rows problem

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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.)
    Last edited by shades; 01-05-05 at 17:55.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Dec 2004
    Posts
    78

    Question

    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
    Last edited by bhavesh78; 01-05-05 at 20:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •