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

---
Regards,
Norman.

"CLR" <crob...@tampabay.rr.com> wrote in message
news:ONMD9Gg...@TK2MSFTNGP11.phx.gbl...

[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

---
Regards,
Norman

"Norman Jones" <norma...@whereforartthou.com> wrote in message
news:OeNFlTgZ...@TK2MSFTNGP11.phx.gbl...

[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

"Norman Jones" <norma...@whereforartthou.com> wrote in message

news:e0oq9WgZ...@TK2MSFTNGP10.phx.gbl...

[CLR]

Hi Norman...........

I notice that my access of the microsoft.public.excel.programming here at
work (through msnews.microsoft.com) does not show either of our posts from
Saturday. I guess they have been deleted for some reason.........wonder
whut's up with that? They're still on Google of course, and fortunately I
had saved your code and emailed it to myself here at work. At any rate, I
just wanted to thank you again for helping me.........I installed your code
this morning and it works "finer than frog hair". With your kind help, my
local "SuperHero" status has been restored.....now I must go "press my
cape"<g>

Thanks again,