Cell Bkgnd/Text color Change while Protected Sheets Grouped

[RaudelJr]

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

[HansV]

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:

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

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.

[RaudelJr]

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?

[HansV]

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:

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

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.

[RaudelJr]

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.

[HansV]

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

[RaudelJr]

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.

[HansV]

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:

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

If you assign this macro to a Quick Access Toolbar button for that workbook, it will be available in all sheets.

[RaudelJr]

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.

[HansV]

You could add the following lines below the line with PasteSpecial:

Code: Select all

                wsh.Select
                wsh.Range("A7").Select

and if you wish, you can select the Instructions sheet, or Sheet 1, again at the end of the macro.

[RaudelJr]

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

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

and

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

[HansV]

Glad to have been able to help!

[HansV]

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

[RaudelJr]

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.

[HansV]

A weird issue indeed. Stick with your version then!

[RaudelJr]

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...