zamboni
09-07-2016, 05:39 AM
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
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