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

Question

Hi,

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.

Answer 1

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.

Answer 2

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

Answer 3

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