Disable Format Painter?

[CLR]

Hi All...........

I have protected a worksheet/workbook through the use of the menus, yet
users can still use the Format Painter on the un-protected cells, even tho
the regular formatting options are turned off by the Protection........is
there any way to disable the Format Painter also?..........hopefully
something I could add to the WorkbookOpen Macro......

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Norman Jones]

Hi Chuck,

One way would be to use the workbook activate event to disable thr format
painter and the workbook deactivate event to re-enable it. This way, the
format painter is available to any other workbook.

Right-click the Excel icon to the left of 'File' on your menu bar and paste
the following code into the workbook's ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Not ThisWorkbook.Name = "Book.XLT" Then
ThisWorkbook.Names.Add Name:="LastSaved", RefersTo:=Now
ThisWorkbook.Names("LastSaved").Visible = False
Else

End If
End Sub

Private Sub Workbook_Deactivate()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars("Standard").Controls
If ctl.Caption = "&Format Painter" Then
ctl.Enabled = True
End If
Next
End Sub

 

[Norman Jones]

Hi Chuck,

Cut and paste misfired - operator error!

Replace the previous code with:

Private Sub Workbook_Activate()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars("Standard").Controls
If ctl.Caption = "&Format Painter" Then
ctl.Enabled = False
End If
Next
End Sub


Private Sub Workbook_Deactivate()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars("Standard").Controls
If ctl.Caption = "&Format Painter" Then
ctl.Enabled = True
End If
Next
End Sub

 

[CLR]

lolol.............thanks Norman.......I was just delicately trying to
compose a response requesting some "clarification"........this one makes a
LOT more sense, even to my limited VBA grasp..........it really does look
good, and I'm sure it will behave exactly like I want.......

Thanks again muchly.........

Vaya con Dios,
Chuck, CABGx3