Set a shortcut key for "Copy Format"

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Hi,

Is there a way to set a shortcut key for "Copy Format" ?
Or use VBA to imitate the usual mouse-click / mouse-double-click function of "Copy Format"


Thanks a lot!
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Re: [VBA] Set a shortcut key for "Copy Format"

Hi,

You can record your own macro ... and give it the shortcut you want ...
 

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Re: [VBA] Set a shortcut key for "Copy Format"

Hi James,
But the macro cannot imitate the mouse-double-click function (continual paste of format ) of "Copy Format"
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Re: [VBA] Set a shortcut key for "Copy Format"

Hi,

Sorry ... do not understand you remark ...

If you need to repeat a Copy Format you have just made ... you can use the function key F4
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,966
Office Version
365
Platform
Windows
Re: [VBA] Set a shortcut key for "Copy Format"

Cross posted https://chandoo.org/forum/threads/vba-set-a-shortcut-key-for-copy-format.42859/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,466
Office Version
2019
Platform
Windows
Re: [VBA] Set a shortcut key for "Copy Format"

Do you want copy or paste format?

Ctrl + Alt + v, t, enter
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Re: [VBA] Set a shortcut key for "Copy Format"

What do you call :

the usual mouse-click / mouse-double-click function of "Copy Format"
 

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Re: [VBA] Set a shortcut key for "Copy Format"

Problem solved. Thanks all. I use the following VBA code.

Code:
Private [I]Sub[/I] Workbook_Open[I]([/I][I])[/I]
    On Error Resume Next
    Application.ScreenUpdating = False
    Dim SC_Dict As [I]Object[/I]
    
    ' ________________ [ OnKey ] ________________
    '   Ctrl  Shift  Alt
    '   ^    +    %
    
    Set SC_Dict = Create[I]Object[/I][I]([/I]"Scripting.Dictionary"[I])[/I]
    With SC_Dict
        
        .Add "^+[I]{[/I]v[I]}[/I]"[I],[/I] "PasteFormats"
        
        For Each k In .Keys
            Application.OnKey k[I],[/I] Me.CodeName & "." & SC_Dict[I]([/I]k[I])[/I]
        Next
        '---------------------
    End With
[I]End Sub[/I]



[I]Sub[/I] PasteFormats[I]([/I][I])[/I]
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteFormats[I],[/I] Operation:=xlNone[I],[/I] SkipBlanks:=False[I],[/I] Transpose:=False
    On Error GoTo 0
[I]End Sub[/I]
 
Last edited:

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Re: [VBA] Set a shortcut key for "Copy Format"

usual mouse-click function of "Copy Format" : only paste format once
mouse-double-click function of "Copy Format" : paste format many times upon user's clicks
 

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Re: [VBA] Set a shortcut key for "Copy Format"

What do you call :

the usual mouse-click / mouse-double-click function of "Copy Format"
usual mouse-click function of "Copy Format" : only paste format once
mouse-double-click function of "Copy Format" : paste format many times upon user's clicks
 

Forum statistics

Threads
1,086,088
Messages
5,387,744
Members
402,076
Latest member
Baskaran Arumugam

Some videos you may like

This Week's Hot Topics

Top