The control will remain on the Cell command bar until the Excel application
ends, even if you close the workbook that created it. If you want the
control to be accessible only when that one workbook is active, use code
like the following in the ThisWorkbook code module:


Private Const C_TAG = "MyFormatPainter"

Private Sub Workbook_Activate()

Dim Ctrl As Office.CommandBarControl
Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
If Ctrl Is Nothing Then
Set Ctrl = CreateControl
End If
Ctrl.Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars.FindControl(Tag:=C_TAG).De lete
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars.FindControl(Tag:=C_TAG).Vi sible = False
End Sub

Private Function CreateControl() As Office.CommandBarControl
Dim C As Office.CommandBarControl
Set C = Application.CommandBars("Cell").Controls.Add(ID:=1 08,
temporary:=True)
C.Tag = C_TAG
Set CreateControl = C
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Jim May" wrote in message
...
I only want this in a sample file - not all future or current workbooks.
Would I not add the code you provided to my Workbook.open
and then don't I need a On Workbook.Close
code to Remove it?

Thanks,

Jim May

"Chip Pearson" wrote:

Jim,

The following code will add the Format Painter to the right-click menu:

Application.CommandBars("Cell").Controls.Add ID:=108, temporary:=True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Jim May" wrote in message
...
I was trying to replicate the format painter icon (on the toolbar) and
place
same
option on the short-cut menu. performed the below via a recorded
macro,
but
nee to be able the

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/13/2007 by Jim May

Selection.Copy
ActiveCell.Offset(4, 2).Range("A1").Select ' << Needs to be a
Clicked
cell in the spreadsheet - but how can I speak to that here???????
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
End Sub