Macro to select the Format Painter?

[Terry Pinnell]

(Mistakenly posted earlier in microsoft.public.excel.misc)

I use the Format Painter a lot and often it would be a bit faster
to activate it with the keyboard instead of clicking the tool
icon. I don't believe there is one built into Excel 365 so I tried
the trivial macro below. However, that and various other versions
I tried all fail.

How do I simply get it to simply click the Format Painter and copy
the active cell please, ready for pasting that format elsewhere?

'
Sub ClickFormatPainter()
'
' Select cell to copy then activate Format Painter, ready to paste
elsewhere.
' Keyboard Shortcut: Ctrl+Shift+F
'
ActiveCell.Address.Select
Selection.Copy
End Sub

P.S: I have meanwhile implemented a suggestion made elsewhere, to move
the Format Painter to the left hand side of the QAT, and use Alt+1. But
I'd stll like to know if a macro can do it please?


Terry

[Claus Busch]

Hi Terry,

Am Fri, 30 Jul 2021 17:54:57 +0100 schrieb Terry Pinnell:

> (Mistakenly posted earlier in microsoft.public.excel.misc)
>
> I use the Format Painter a lot and often it would be a bit faster
> to activate it with the keyboard instead of clicking the tool
> icon. I don't believe there is one built into Excel 365 so I tried
> the trivial macro below. However, that and various other versions
> I tried all fail.
>
> How do I simply get it to simply click the Format Painter and copy
> the active cell please, ready for pasting that format elsewhere?

why don't you paste the formats with VBA directly?

Sub CpyFormat()
Range("A1").Copy
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub


Regards
Claus B.
--
Windows10
Microsoft 365 for business

[Terry Pinnell]

Thanks, good to hear from you Claus.

I don't follow your suggestion, which pastes the format of a fixed cell,
A1.

I want the macro to copy the format of whatever cell is currently
selected. I can then select the target (e.g. by clicking it, or moving
to it) and the copied format will be immediately applied to it.

That's what happens when I use <Alt+1>.

Best wishes,

Terry

====================

[Adrian Caspersz]

On 30/07/2021 17:54, Terry Pinnell wrote:
> (Mistakenly posted earlier in microsoft.public.excel.misc)
>
> I use the Format Painter a lot and often it would be a bit faster
> to activate it with the keyboard instead of clicking the tool
> icon.

https://www.infopathdev.com/blogs/greg/archive/2005/06/27/Lock-the-Format-Painter-for-Repeated-Usage.aspx


--
Adrian C