Avatar of anjana81
anjana81
 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
Microsoft Excel

Avatar of undefined
Last Comment
anjana81

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Arno Koster

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes