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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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