How to allow select all sheets in a protected workbook

anjana81 used Ask the Experts™
   I would like to allow users to selcet all sheets in a Excel workbook in which all the sheets are protected.Allow them to
1. copy all the sheets and paste only the values without the formulas.
2. when they click inside the sheet then the workbook is protected again.

The second part work fine with me.But when I try to select all the sheets and unprotect the shets the sheets are ungrouped and the active sheet values are copied and pasted.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Selection.Address = Cells.Address Then
            ActiveSheet.Unprotect PWD

    ElseIf ActiveSheet.ProtectContents = False Then
            Call LockCells
            Call UnlockCells
    End If

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)


End Sub
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could use this loop to perform unprotect / copy / etc on all selected sheets:

For Each sh In ActiveWindow.SelectedSheets
     sh.Unprotect PWD
Next sh

Open in new window


I am able to unprotect the sheets. But the problem is that I put this code in Worksheet_BeforeRightClick . But the next time when i right click to copy then sheets are ungrouped(No longer all sheets are selected)

where exactly should I place the code.
I'm using office2010, when i select multiple worksheets and right-mouse-click on a cell in the active worksheet, the worksheet selection is kept intact. Apperantly you are using a different office version.

You could assign a key combination to the macro, such as [Alt-F9], or you could place a button in the excel menu bar that fires the routine containing the code.


Thanks akoster.

I used the below code and was able to unprotect the sheet and then group all and this works fine.

If (ActiveWindow.SelectedSheets.Count > 1) Then
        Set oSheets = ActiveWindow.SelectedSheets        
          For Each oSheet In oSheets
            With oSheet
                oSheet.Unprotect PWD
            End With
        Next oSheet
End If

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial