Enable Group/Ungroup in protected sheet

Anonymous
2013-01-02T12:41:00+00:00

Dear solver,

 I have an excel sheet which is renamed as "Emp Summary" which is protected without password.

 In this protected sheet, I want to enable the Group/Ungroup option to show/hide row no. 34 to 42.

 How to do that? Will macro must be require? If yes, then please write the code for me. I would appreciate if this could be done withouth a macro, 

 even by other trick/option.

 Also, if I send the same file to another user, will he/she also requires to enable macro at his/her end?

 Please help.

 Many thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2013-01-02T12:56:30+00:00

You need VBA for this, and the end user will need to allow macros for this to work.

Press Alt+F11 to activate the Visual Basic Editor.

Double-click ThisWorkbook, under Microsoft Excel Objects in the project explorer on the left hand side.

Copy the following code into the module that appears:

Private Sub Workbook_Open()

    With Worksheets("Emp Summary")

        .EnableOutlining = True

        .Protect UserInterfaceOnly:=True

    End With

End Sub

This code will be executed automatically each time the workbook is opened.

Was this answer helpful?

300+ people found this answer helpful.
0 comments

91 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2015-09-09T14:29:44+00:00

    Hi, I followed your advice and have following problem:

    When I re-open the workseet, password is required which is ok and I can still use my combo boxes macro & group/ungroup lines. But this also unlock all lock cells. (Sheet is behaving like unprotected eventhough it is still protected.)

    I have to manualy go to review --> unprotect (no password is required anymore), protect it again and then everything works how it should. (i.e. locked cells are locked,combobox macro works and I can ungroup/group).

    Is there a way how to still keep cells protected & being able to group/ungroup & being able to use combobox macro without this additional step ?

    I copy here a VB scripts I use:

    WORKBOOK:

    Private Sub Workbook_Open()

         Dim wsh As Variant

         For Each wsh In Worksheets(Array("Bench work", "To JMP"))

             wsh.EnableOutlining = True

                   wsh.Protect UserInterfaceOnly:=True, _

                 DrawingObjects:=False

         Next wsh

     End Sub

    WORKSHEET:

    '==========================

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

      Cancel As Boolean)

    Dim str As String

    Dim cboTemp As OLEObject

    Dim ws As Worksheet

    Set ws = ActiveSheet

    Set cboTemp = ws.OLEObjects("TempCombo")

      On Error Resume Next

      With cboTemp

      'clear and hide the combo box

        .ListFillRange = ""

        .LinkedCell = ""

        .Visible = False

      End With

    On Error GoTo errHandler

      If Target.Validation.Type = 3 Then

        'if the cell contains a data validation list

        Cancel = True

        Application.EnableEvents = False

        'get the data validation formula

        str = Target.Validation.Formula1

        str = Right(str, Len(str) - 1)

        With cboTemp

          'show the combobox with the list

          .Visible = True

          .Left = Target.Left

          .Top = Target.Top

          .Width = Target.Width + 5

          .Height = Target.Height + 5

          .ListFillRange = str

          .LinkedCell = Target.Address

        End With

        cboTemp.Activate

        'open the drop down list automatically

        Me.TempCombo.DropDown

      End If

    errHandler:

      Application.EnableEvents = True

      Exit Sub

    End Sub

    '=========================================

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim str As String

    Dim cboTemp As OLEObject

    Dim ws As Worksheet

    Set ws = ActiveSheet

    Application.EnableEvents = False

    Application.ScreenUpdating = True

    If Application.CutCopyMode Then

      'allow copying and pasting on the worksheet

      GoTo errHandler

    End If

    Set cboTemp = ws.OLEObjects("TempCombo")

      On Error Resume Next

      With cboTemp

        .Top = 10

        .Left = 10

        .Width = 0

        .ListFillRange = ""

        .LinkedCell = ""

        .Visible = False

        .Value = ""

      End With

    errHandler:

      Application.EnableEvents = True

      Exit Sub

    End Sub

    Was this answer helpful?

    0 comments
  2. HansV 462.7K Reputation points MVP Volunteer Moderator
    2015-09-09T15:32:31+00:00

    I can't explain that, sorry.

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2016-03-28T10:48:40+00:00

    Hi HansV MVP

    It happens to me the same thing, when opening the workbook I'm prompted for the password, could anyone help out with this issue? it is driving me insane!

    Thanks.

    Was this answer helpful?

    0 comments