Protect cells and allow grouping/un-grouping

sfreuden · December 12th, 2006, 08:11 PM

How do I build a macro that will not allow users to change the contents of the cells but allow them to group and un-group rows?

jrogers · December 13th, 2006, 05:23 AM

first you need to protect the sheet.. then you have a number of options. a good one is to have a make a rightclick event to ungroup/group , something a bit like this

Code:

Sub RunThisFirst()
    Application.CommandBars("Cell").Reset
    With Application.CommandBars("Cell").Controls.Add
        .Caption = "Ungroup / Group"
        .OnAction = "GroupUngroup"
        .Tag = "GroupUngroup"
    End With

End Sub


Sub GroupUngroup()


    If Rows(ActiveCell.Row - 1).Hidden = True Then
       ExecuteExcel4Macro "SHOW.DETAIL(1," & ActiveCell.Row & ",TRUE)"
    Else
       ExecuteExcel4Macro "SHOW.DETAIL(1," & ActiveCell.Row & ",FALSE)"
    End If

End Sub

sfreuden · December 13th, 2006, 08:00 PM

jrogers,

I am still having trouble. Can I possibly send you the document?

Thanks for your help,
Scott

sfreuden · December 14th, 2006, 12:01 AM

What does the Sub RunThisFirst do?

jrogers · December 14th, 2006, 08:01 AM

run this macro first.. either as an on open. ths will creat an option when you right click that gives you an option to ungroup... you need to unprotect the sheet frist, then reprotect after