Cell Bkgnd/Text color Change while Protected Sheets Grouped
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Cell Bkgnd/Text color Change while Protected Sheets Grouped
Allow Cell Background Fill/Text Color Change while Protected Sheets are Grouped
I've posted in other forums with no replies. Not really sure how to proceed.
I'm working on a Template that will be Themed buy a few members on a monthly basis.
All the Sheets are protected and the user is to basically just Background Fill the Cells, etc.
All Sheets are to be Themed Identically. But to avoid doing the same thing on each Sheet, they group the sheets first and then Theme just the first sheet which in turn carries the same to the rest of the sheets.
Unfortunately, Excel gives an error when changing the background with all sheets grouped even when all the
[ ] checkboxes are selected upon protecting the sheets.
Is there away to make theming of all the sheets at once while keeping them protected?
I want to prevent the users from editing the text and the structure of the sheets/workbook but have full freedom to format the cells background/colors/
My Sample Workbook:
https://drive.google.com/open?id=0B5MLw ... TJfZ29Ibjg" onclick="window.open(this.href);return false;
I've tested code such as:
Private Sub Workbook_Open()
For Each ws In Sheets
With ws
.Unprotect Password:="Protect"
.Protect Password:="Protect", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
.EnableOutlining = True
End With
Next ws
End Sub
Where I change Trues to False, but the only one that works is
Content:=False
But that defeats the purpose since all content is editable if all content is allowed.
Any ideas?
Thank you for the help in advance.
Raudel
I've posted in other forums with no replies. Not really sure how to proceed.
I'm working on a Template that will be Themed buy a few members on a monthly basis.
All the Sheets are protected and the user is to basically just Background Fill the Cells, etc.
All Sheets are to be Themed Identically. But to avoid doing the same thing on each Sheet, they group the sheets first and then Theme just the first sheet which in turn carries the same to the rest of the sheets.
Unfortunately, Excel gives an error when changing the background with all sheets grouped even when all the
[ ] checkboxes are selected upon protecting the sheets.
Is there away to make theming of all the sheets at once while keeping them protected?
I want to prevent the users from editing the text and the structure of the sheets/workbook but have full freedom to format the cells background/colors/
My Sample Workbook:
https://drive.google.com/open?id=0B5MLw ... TJfZ29Ibjg" onclick="window.open(this.href);return false;
I've tested code such as:
Private Sub Workbook_Open()
For Each ws In Sheets
With ws
.Unprotect Password:="Protect"
.Protect Password:="Protect", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
.EnableOutlining = True
End With
Next ws
End Sub
Where I change Trues to False, but the only one that works is
Content:=False
But that defeats the purpose since all content is editable if all content is allowed.
Any ideas?
Thank you for the help in advance.
Raudel
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Welcome to Eileen's Lounge!
Even if you allow formatting in protected sheets, Excel won't let you apply formatting if you select multiple sheets; you can only apply formatting to one sheet at a time.
It's possible to write VBA code that will apply formatting to multiple sheets, or to copy formatting from the Week 1 sheet to the Week 2 to Week 5 sheets:
Warning: currently, B7:B8 and C7:C8 are merged on Week 1, but on Week 2 etc., B7 and C7 are merged. It'd be best to make sure that the same cells are merged on each sheet, to prevent unexpected effects.
Even if you allow formatting in protected sheets, Excel won't let you apply formatting if you select multiple sheets; you can only apply formatting to one sheet at a time.
It's possible to write VBA code that will apply formatting to multiple sheets, or to copy formatting from the Week 1 sheet to the Week 2 to Week 5 sheets:
Code: Select all
Sub CopyFormatting()
Dim wsh As Worksheet
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets("Week 1").UsedRange.Copy
For Each wsh In Worksheets(Array("Week 2", "Week 3", "Week 4", "Week 5"))
wsh.UsedRange.PasteSpecial xlPasteFormats
Next wsh
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Thank you HansV.
Thank you for bringing up the differences in merged cells. I was in the process of modifying it and didn't get through all.
I tested the code and it works.
How can I have it happen automatically after every formatting action so that I don't have to assign a button to do that task?
Thank you for bringing up the differences in merged cells. I was in the process of modifying it and didn't get through all.
I tested the code and it works.
How can I have it happen automatically after every formatting action so that I don't have to assign a button to do that task?
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
That wouldn't be easy - there is no event that occurs when the user changes the formatting of cells. I think a command button would be best, but as an alternative, you could run code when one of the worksheets Week 2 to Week 5 is activated.
In the ThisWorkbook module:
However, be aware that this code will make it impossible to copy anything from another sheet into Sheet 2 etc., because the clipboard will be cleared when the sheet is activated.
In the ThisWorkbook module:
Code: Select all
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Week 2", "Week 3", "Week 4", "Week 5"
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets("Week 1").UsedRange.Copy
Sh.UsedRange.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Select
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
I understand. That could bring out other issues.
One issue I'm looking at now too is that the Week 1, Week 2, Week 3, Week 4, Week 5 Tabs will be renamed by the same user that will be theming the Workbook.
They will be naming them something such as 05-01-17, 05-08-17, 05-15-17, 05-22-17, 05-29-17.
These Sheet Names in Turn will update Cell K5 in all Sheets.
Is there a way to modify the CopyFormatting Code to Look for the Sheet's Parent Name without entering a Specific Sheet name into the code?
Thank you very much for the help.
This is really putting things into perspective.
Raudel.
One issue I'm looking at now too is that the Week 1, Week 2, Week 3, Week 4, Week 5 Tabs will be renamed by the same user that will be theming the Workbook.
They will be naming them something such as 05-01-17, 05-08-17, 05-15-17, 05-22-17, 05-29-17.
These Sheet Names in Turn will update Cell K5 in all Sheets.
Is there a way to modify the CopyFormatting Code to Look for the Sheet's Parent Name without entering a Specific Sheet name into the code?
Thank you very much for the help.
This is really putting things into perspective.
Raudel.
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Let's say that you want to copy the formatting to all sheets except Instructions (and the sheet containing the button, of course):
Code: Select all
Sub CopyFormatting()
Dim wsh As Worksheet
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.UsedRange.Copy
For Each wsh In Worksheets
Select Case wsh.Name
Case ActiveSheet.Name, "Instructions"
' Skip this sheet
Case Else
' Copy the formatting
wsh.UsedRange.PasteSpecial xlPasteFormats
End Select
Next wsh
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Thank you HansV
I have applied your suggestions, and instead of a button I assigned a Shortcut key Ctrl+T
Google Drive Link:
https://drive.google.com/open?id=0B5MLw ... TJfZ29Ibjg" onclick="window.open(this.href);return false;
I couldn't attach it because it's a template *.xltm
I've protected the Sheets and I've Protected the Workbook.
Everything seems to be going to work as needed.
One last Item if possible. Since The users will be Renaming the Sheets as needed....
Is there a way to allow them to Rename the Sheets freely, while restricting them from Deleting them or changing the workbook Structure? (which is why I'm attempting to Protect the Workbook as well)
The File is currently locked by sheet and workbook, but I would have to unprotect the workbook if there is no way around it.
I really do appreciate all the help you've given me through this.
Raudel.
I have applied your suggestions, and instead of a button I assigned a Shortcut key Ctrl+T
Google Drive Link:
https://drive.google.com/open?id=0B5MLw ... TJfZ29Ibjg" onclick="window.open(this.href);return false;
I couldn't attach it because it's a template *.xltm
I've protected the Sheets and I've Protected the Workbook.
Everything seems to be going to work as needed.
One last Item if possible. Since The users will be Renaming the Sheets as needed....
Is there a way to allow them to Rename the Sheets freely, while restricting them from Deleting them or changing the workbook Structure? (which is why I'm attempting to Protect the Workbook as well)
The File is currently locked by sheet and workbook, but I would have to unprotect the workbook if there is no way around it.
I really do appreciate all the help you've given me through this.
Raudel.
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
If you protect the structure of the workbook, users won't be able to rename sheets. You could provide a macro that unprotects the workbook, renames the active sheet, then protects the workbook again:
If you assign this macro to a Quick Access Toolbar button for that workbook, it will be available in all sheets.
Code: Select all
Sub RenameSheet()
Dim strName As String
On Error GoTo ErrHandler
strName = InputBox("Enter new name for the active sheet")
If strName <> "" Then
ActiveWorkbook.Unprotect Password:="secret"
ActiveSheet.Name = strName
End If
ExitHandler:
On Error Resume Next
ActiveWorkbook.Protect Password:="secret", Structure:=True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Thank you HansV.
I have applied all your suggestions with shortcuts.
One item that is not really an issue but, that is mostly a preference, is that after pasting the formatting to all sheets, those sheets remain with all cells selected.
I tried to modify your code a bit to select cell A7, with the intention of ending the selection on all cells of all sheets, but that only acted on the active worksheet.
How can I accomplish this?
Thanks again.
I have applied all your suggestions with shortcuts.
One item that is not really an issue but, that is mostly a preference, is that after pasting the formatting to all sheets, those sheets remain with all cells selected.
I tried to modify your code a bit to select cell A7, with the intention of ending the selection on all cells of all sheets, but that only acted on the active worksheet.
How can I accomplish this?
Thanks again.
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
You could add the following lines below the line with PasteSpecial:
and if you wish, you can select the Instructions sheet, or Sheet 1, again at the end of the macro.
Code: Select all
wsh.Select
wsh.Range("A7").Select
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Thank you HansV,
I've applied all your suggestions. And Included Worksheets(1).Activate in CopyFormatting to ensure only Formatting from the first worksheet is copied Since I opted to use Shortcuts instead of buttons. Also Worksheets(1).Activate included at the end so that the first worksheet is always the last one that remains activated.
Also your RenameSheet Codes Works like a Charm, also with Shortcuts instead of a button, Thank you!
I think this is the Final Version:
https://drive.google.com/open?id=0B5MLw ... Dd5WnJyRVk" onclick="window.open(this.href);return false;
If you have any other suggestions, I'm all ears
and
Thank you very much for all the help provided.
Raudel
I've applied all your suggestions. And Included Worksheets(1).Activate in CopyFormatting to ensure only Formatting from the first worksheet is copied Since I opted to use Shortcuts instead of buttons. Also Worksheets(1).Activate included at the end so that the first worksheet is always the last one that remains activated.
Also your RenameSheet Codes Works like a Charm, also with Shortcuts instead of a button, Thank you!
I think this is the Final Version:
https://drive.google.com/open?id=0B5MLw ... Dd5WnJyRVk" onclick="window.open(this.href);return false;
If you have any other suggestions, I'm all ears
Code: Select all
Sub CopyFormatting()
Dim wsh As Worksheet
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets(1).Activate
ActiveSheet.UsedRange.Copy
ActiveSheet.Range("A9").Select
For Each wsh In Worksheets
Select Case wsh.Name
Case ActiveSheet.Name, "Instructions", "Drop Downs"
' Skip this sheet
Case Else
' Copy the formatting
wsh.UsedRange.PasteSpecial xlPasteFormats
' Select cell A9 to clear selection of all Cells in all sheets
wsh.Select
wsh.Range("A9").Select
End Select
Next wsh
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Worksheets(1).Activate
End Sub
Code: Select all
Sub RenameSheet()
Dim strName As String
On Error GoTo ErrHandler
strName = InputBox("Enter new name for the active sheet")
If strName <> "" Then
ActiveWorkbook.Unprotect Password:="protect"
ActiveSheet.Name = strName
End If
ExitHandler:
On Error Resume Next
ActiveWorkbook.Protect Password:="protect", Structure:=True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Thank you very much for all the help provided.
Raudel
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
Glad to have been able to help!
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
It isn't necessary to select the first sheet at the beginning of CopyFormatting; you only have to do that at the end of the macro:
Code: Select all
Sub CopyFormatting()
Dim wsh As Worksheet
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Worksheets(1).UsedRange.Copy
For Each wsh In Worksheets
Select Case wsh.Name
Case Worksheets(1).Name, "Instructions", "Drop Downs"
' Skip this sheet
Case Else
' Copy the formatting
wsh.UsedRange.PasteSpecial xlPasteFormats
' Select cell A9 to clear selection of all Cells in all sheets
wsh.Select
wsh.Range("A9").Select
End Select
Next wsh
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Worksheets(1).Activate
Range("A9").Select
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
I did that because I tested it, and for a weird reason if the user ran the copy formatting macro on, say, week 5 sheet, it would unmerge the first cells of some of the sheets. Not sure why.
So I figured I could force the formatting copy of the first sheet only to avoid that weird issue.
Unless there is another solution.
So I figured I could force the formatting copy of the first sheet only to avoid that weird issue.
Unless there is another solution.
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
A weird issue indeed. Stick with your version then!
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 136
- Joined: 17 Apr 2017, 19:16
Re: Cell Bkgnd/Text color Change while Protected Sheets Grou
I'll look at it again tomorrow. But I just couldn't figure out why that happened. But in this specific workbook, themeing is meant to be from the 1st sheet anyway. But curiosity has me going...