Disable Format Painter

[George Rodine]

I have a protected worksheets that users cannot change format on unprotected cells; however users can change format of cells by use of "Format Painter". This messes up conditional formatting. How can I disable Format Painter so that it is not usable on this workbook. I'm using Excel 2010. Thank you.

 

[NARAYANK991]

Hi ,

At first I thought it was the same question !

See this link :

http://www.pcreview.co.uk/threads/disable-format-painter.982886/

Narayan

 

[George Rodine]

I saw this in my prior search and tried this code, but I can still format paint over the unprotected cells. Anyone know what this code is missing. Does the Activate code indicate when you open the workbook it runs the code (disables the format painter) and the Deactivate enables the format painter when you close the workbook? Again, I'm trying to disable the format painter so it cannot be used. I am using Excel 2010. Thank you!

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
______________________________________________________________

Mod edit : thread moved to appropriate forum !​