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(
If Selection.Address = Cells.Address Then
ActiveSheet.Unprotect PWD
ElseIf ActiveSheet.ProtectContent
Call LockCells
Call UnlockCells
End If
End Sub
Private Sub Worksheet_BeforeRightClick
Unprotect_All_Sheets
End Sub
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(
If Selection.Address = Cells.Address Then
ActiveSheet.Unprotect PWD
ElseIf ActiveSheet.ProtectContent
Call LockCells
Call UnlockCells
End If
End Sub
Private Sub Worksheet_BeforeRightClick
Unprotect_All_Sheets
End Sub
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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.SelectedShee
Set oSheets = ActiveWindow.SelectedSheet
For Each oSheet In oSheets
With oSheet
oSheet.Select
oSheet.Unprotect PWD
End With
Next oSheet
oSheets.Select
End If
I used the below code and was able to unprotect the sheet and then group all and this works fine.
If (ActiveWindow.SelectedShee
Set oSheets = ActiveWindow.SelectedSheet
For Each oSheet In oSheets
With oSheet
oSheet.Select
oSheet.Unprotect PWD
End With
Next oSheet
oSheets.Select
End If
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
where exactly should I place the code.