Intercepting Paste

I have a spreadsheet that I intercept the paste command so to only paste values from within the workbook.

Its working great at:

    'Paste Special - Values Only
    Application.OnKey "^v", "PasteValues"


Paste Value moduel:
    Selection.PasteSpecial Paste:=xlPasteValues

The only issue is when I try to paste in values from outside of Excel... doing this causes an error "Paste method of class range paste fail"

I tried the following:
    'ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

Which works for pasting text from outside of Excel but errors when paste from within Excel.

So my solution which works but doesn't seem like a good solution is:

    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteValues 'From within workbook
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False 'From outside paste


My question, is there a better way?

Thank you.
thandelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you don't have a userform in your workbook, add one. It provides access to the DataObject object which is needed in VBA to access the Clipboard.


Sub PasteValues()

Dim MyData As DataObject
Dim intHeight As Integer

intHeight = ActiveCell.RowHeight
Set MyData = New DataObject
MyData.GetFromClipboard
ActiveCell.Value = MyData.GetText
ActiveCell.RowHeight = intHeight

End Sub

Open in new window

0
thandelAuthor Commented:
I am not familiar with a user form... but I am just looking to control the paste to paste text only.... can I use your solution in this manner or do I need this user form with your solution?
0
Martin LissOlder than dirtCommented:
You won't actually see or use the userform. It's just there to supply the DataObject that is needed. To add a userform go to VisualBasic and then Insert|UserForm. I'ver attached the messy wb I used for testing.
Q-28223421.xlsm
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

thandelAuthor Commented:
Thanks can you post your file in an XLS format... as I have Excel 2003
0
Martin LissOlder than dirtCommented:
0
thandelAuthor Commented:
Thank you.  when I paste values into a new cell, its add a special character and a line break....


"xxx"  pastes as: "xxx

"
0
Martin LissOlder than dirtCommented:
Okay I didn't realize that so try this.
Sub PasteValues()

Dim MyData As DataObject
Dim intPos As Integer

Set MyData = New DataObject
MyData.GetFromClipboard
intPos = InStr(1, MyData.GetText, vbCrLf)
If intPos > 0 Then
    ActiveCell.Value = Left(MyData.GetText, intPos - 1)
Else
    ActiveCell.Value = MyData.GetText
End If

End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Did that help?
0
thandelAuthor Commented:
Its pasting better however if the text contains a tab (outside Excel text) or multipul cells are copied from within Excel its pasting a special character instead of and tab to the next cell.
0
Martin LissOlder than dirtCommented:
I think that your original solution is the best way to proceed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.