asked on

How to allow select all sheets in a protected workbook

Hi,
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)

Unprotect_All_Sheets

End Sub

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anjana81

ASKER

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.

Arno Koster

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.

anjana81

ASKER

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.Select
oSheet.Unprotect PWD
End With
Next oSheet

oSheets.Select
End If