how can i place the Format Painter icon on the Context menu?

[Guest]

When working on repetitive tasks I would like to have the Format Painter Icon
on my menu when I right click on a cell. How can I place it there?

 

[Guest]

You cant unless you want to use VBA.
You can always move the toolbar to a more convinient position

 

[Dave Peterson]

One way:

Option Explicit
Sub auto_open()

On Error Resume Next
Application.CommandBars("cell").Controls("Format Painter").Delete
On Error GoTo 0

With CommandBars("Cell")
With .Controls.Add(msoControlButton, temporary:=True)
.Caption = "Format Painter"
.FaceId = Application.CommandBars("standard") _
.FindControl(ID:=108).FaceId
.OnAction = ThisWorkbook.Name & "!formatpainter"
End With
End With

End Sub
Sub FormatPainter()
Application.CommandBars("standard").FindControl(ID:=108).Execute
End Sub

If you put this in a workbook and store that workbook in your XLStart folder,
you'll have added this each time you start excel.

You could also just open that workbook when you want to use it, too.


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Dave,
This was very helpful.
Thank you!

One way:

Option Explicit
Sub auto_open()

On Error Resume Next
Application.CommandBars("cell").Controls("Format Painter").Delete
On Error GoTo 0

With CommandBars("Cell")
With .Controls.Add(msoControlButton, temporary:=True)
.Caption = "Format Painter"
.FaceId = Application.CommandBars("standard") _
.FindControl(ID:=108).FaceId
.OnAction = ThisWorkbook.Name & "!formatpainter"
End With
End With

End Sub
Sub FormatPainter()
Application.CommandBars("standard").FindControl(ID:=108).Execute
End Sub

If you put this in a workbook and store that workbook in your XLStart folder,
you'll have added this each time you start excel.

You could also just open that workbook when you want to use it, too.


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm