paste as text from other applications

[zamboni]

Hi all

I would like to have data which is pasted into a sheet's specific input cell (B8, that is) to always be pasted as values or as text (in cases where the data source is an external application such as a web browser).
In order to achieve this goal I made some tweaks in code that I have used before and which undoes a simple paste and performs a paste special subsequently.
However, my amended code does not work as I want it to. The Worksheet.PasteSpecial method seems to ignore that I want to paste the data as text.
BTW: The Range.PasteSpecial method works just fine - when copying data from within Excel it gets pasted as values only.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range) 'or Target as Excel.Range
'code forces 'simple pasting' to be performed as 'paste values' if data is pasted into cell B8
'Target is a variable which represents the last changed cell


On Error GoTo errorHandler


Application.EnableEvents = False


If Not Intersect(Target, Range("B8")) Is Nothing Then 'If Target.Address = Range("B8") Then <~~ did not work
    If Application.CutCopyMode = xlCopy Then
        Application.Undo
        Target.Select
        ActiveSheet.PasteSpecial Format:="Text" ', NoHTMLFormatting:=True <~~ had no effect
        Target.PasteSpecial Paste:=xlPasteValues
    End If
End If


errorHandler:
Application.EnableEvents = True


End Sub

Any suggestions?

Thanks
Dennis

[GTO]

Greetings Zamboni,

I would suggest trying this: http://www.siddharthrout.com/2011/08...-special-only/

...modified to meet your needs. Regardless of methodology selected, I would add 'And Target.Count = 1 to your IF test.

Hope that helps,

Mark

[zamboni]

Hi GTO

Thanks for your quick reply.

I did as you proposed and tried out the code from the link that you provided. Unfortunately, it seems that the code somehow interferes with the rest of my project so I get a Method 'List' of object '_CommandBarComboBox' failed error message.

So I got back to my original code and changed it to the following:

Private Sub Worksheet_Change(ByVal Target As Range) 'or Target as Excel.Range
'code forces 'simple pasting' to be performed as 'paste values' if data is pasted into cell B8
'Target is a variable which represents the last changed cell


On Error GoTo errorHandler


Application.EnableEvents = False


If Not Intersect(Target, Range("B8")) Is Nothing And Target.Count = 1 Then 'If Target.Address = Range("B8") Then <~~ did not work
    If Application.CutCopyMode = xlCopy Then
        Application.Undo
        Target.Select
        ActiveSheet.PasteSpecial Format:="Text", Link:=False, displayasicon:=False  ', NoHTMLFormatting:=True <~~ had no effect
        Target.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
    End If
End If


errorHandler:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Application.EnableEvents = True


End Sub

This results in an 1004 error at run-time (PasteSpecial method of Worksheet class failed).

I cannot tell which line is causing the error but both PasteSpecial methods show the following expressions which are not being capitalized by the editor:
displayasicon:=False
operation:=xlNone
skipblanks:=False

This is all very frustrating given the fact that I started with the following code (which works like a charm):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'code forces 'simple pasting' to be performed as 'paste values'


On Error Resume Next


If Application.CutCopyMode = xlCopy Then


    Application.EnableEvents = False


    Application.Undo
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text"
    Target.PasteSpecial Paste:=xlPasteValues


    Application.EnableEvents = True


End If


End Sub

Having spent much more time on this than what I deem would be appropriate I am going to stick eventually to the simple yet working code above.

Unless of course, somebody will come up with a bright idea...

Thanks to everyone for reading.
Hope I haven't wasted your time.

Cheers
Dennis

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
  if target.address="$B$8" Then
    Application.enableevents=false

    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
       .GetFromClipboard
       Target = .GetText
    End With

    Application.enableevents=true
  end if
End Sub