Excel VBA Macro: Check content (of clipboard?) before pasting

Question

I've had some serious issues with pasting data from various sources into Excel. Excel tends to try to be smart and does all kinds of silly formating. We need the data as text.

The problem is that we have a lot of users, and many of them are not very experienced with computers, so asking them to use right-click and 'Paste Special' every time is not an option.

I found a solution in recording a macro that uses 'Paste Special' and 'text', and overriding the ctrl-v to use this function. It seemed to work perfectly, until I marked a cell, copied it, and tried to paste it. The macro crashed.

So what I need is a function that can check if I am trying to paste some copied text, and then use this line:

 ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False

While if I am pasting a marked cell, I want to run this line (to paste just the value):

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I am not very experienced in writing VBA macros for Excel (and I hope I never have to be), so if anyone have a few pointers, I'd be most grateful.

score 4 · Answer 1 · edited May 08 '18 at 21:48

4

For clipboard access/manipulation, you'll want to add a reference to the Microsoft Forms 2.0 library in Project->References. You can then use the MSForms.DataObject class that has (among others) a GetFormat method to check whether the clipboard has a particular type of data.

This is a pretty good intro to clipboard handling using DataObject.

edited May 08 '18 at 21:48

Community

1
1

answered Jul 10 '09 at 11:42

Gary McGill

26,400
25
118
202

1

My Excel2013(64bit) on Win7(64bit) did not list Microsoft Forms 2.0 library. I had to choose Tools/Reference/Browse... and select c:/windows/system32/FM20.DLL file. Then was able to use dataobject type. – Whome Sep 10 '14 at 09:39

score 2 · Answer 2 · answered Dec 17 '12 at 12:29

Sub PasteAsText() ' Assign Keyboard Shortcut: Ctrl+v
    Application.ScreenUpdating = False
    Select Case Application.CutCopyMode
        Case Is = False
                On Error Resume Next
                ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
        Case Is = xlCopy
            If Not Range(GetClipboardRange).HasFormula Then
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Else
                ActiveSheet.Paste
            End If
        Case Is = xlCut
            ActiveSheet.Paste
    End Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Function GetClipboardRange() As String
    ' Edited from http://www.ozgrid.com/forum/showthread.php?t=66773
    Dim formats    'Check to make sure clipboard contains table data
    formats = Application.ClipboardFormats
    For Each fmt In formats
        If fmt = xlClipboardFormatCSV Then
            Application.ActiveSheet.Paste Link:=True  'Paste link

            Dim addr1, addr2 As String 'Parse formulas from selection

            addr1 = Application.Substitute(Selection.Cells(1, 1).Formula, "=", "")
            addr2 = Application.Substitute(Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Formula, "=", "")

            GetClipboardRange = addr1 & IIf(addr1 <> addr2, ":" & addr2, "")
            Exit For
        End If
    Next
End Function

score 1 · Answer 3 · answered Sep 28 '09 at 19:43

This isn't the greatest solution, but it technically works. Just try them both.

On Error Resume Next
ActiveSheet.PasteSpecial Format:=Text, Link:=False, DisplayAsIcon:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Joel Goodwin · Answer 4 · 2009-07-10T11:50:12.627

Have you considered making the cells in the target sheet equal to Text? When they're General, Excel does it's best-guess at what you expect to see.

On the other hand if you really want to implement Paste Special...

There is no "Paste" event you can catch - you have catch every place that a paste could occur.

For example, you can capture the CTRL-V keypress if you issue the following code when the workbook starts up (Workbook_Open):

Application.OnKey "^v", "DoMyPaste"

This will call your function instead of the Excel paste function. Put something like this in a module:

Public Sub DoMyPaste()
    If Selection.[is marked cell] Then
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
        ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon _
        := False
    End If
End Sub

I have not tested this, this is more of a rough sketch. Do note that Selection could be more than one cell, so your "check for a marked cell" needs to check the whole range in some way.

This is just the tip of the iceberg though. If you want to a full solution, you should check out this article, which is the OCD version of catching all Paste calls:

http://www.jkp-ads.com/Articles/CatchPaste.asp

Excel VBA Macro: Check content (of clipboard?) before pasting

4 Answers4

Linked