2

Hi everyone I'm fairly new at vba I'm trying to capture all ctrl+v events on my sheet and paste whatever is on the clipboard to the currently selected cell. Most of the time what i want to copy is text from firefox or from notepad E.I client's name or phonenumber that is n our website however the code only works when i try to paste inside de cell itself (in cell edit mode)

    Private Sub CopyPaste()

'PasteSpecial Values Only
  ActiveCell.PasteSpecial Paste:=xlPasteValues, skipblanks:=True

this returns a runtime error 1004 pastespecial method of range class failed i have also tried this but it returns a error too

activecell.PasteSpecial Format:="Text", skipblanks:=True, link:=False, DisplayAsIcon:=False

As a note the main reason for pasting text only and values only is because my excel has a very specific cell layout with colors and other stuff and a normal paste messes everything up.

I would love it if anyone could help me out

Aeon
  • 87
  • 10
  • 2
    You need to access the `Clipboard` - See [here](https://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard) – urdearboy Nov 09 '18 at 20:41
  • 1
    I should have clarified i am capturing all ctrl+v on a workbook_open sub Application.OnKey "^{v}", "CopyPaste" – Aeon Nov 09 '18 at 20:48
  • urdearboy oh so if i do not retrieve the data from the clipboard first i can't paster it as text ? so i need to create a msform obj and paste it there ? – Aeon Nov 09 '18 at 20:52
  • You may find [this](https://www.mrexcel.com/forum/excel-questions/1048884-paste-clipboard-content-certain-cell-vba.html) to be useful – cybernetic.nomad Nov 09 '18 at 21:22

1 Answers1

3

Range.PasteSpecial method pastes a Range that has been copied into the specified Range. So this will work for Ranges only:

ActiveCell.PasteSpecial Paste:=xlPasteValues, skipblanks:=True

Clipboard needs to be accessed directly. How to use clipboard e.g. here.

Option Explicit

Sub Init()
    Application.OnKey "^{v}", "CopyPaste"
End Sub

Public Sub CopyPaste()       
    ' MSForms.DataObject can be used when MSForms lib. is referenced
    Dim clipboard As Variant 

    Dim strContents As String

    ' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
    ' When no MSForms is referenced yet.
    Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 
    clipboard.GetFromClipboard
    strContents = clipboard.GetText
    ' Parse or format strContent if needed
    ActiveCell.Value = strContents
End Sub
Daniel Dušek
  • 13,683
  • 5
  • 36
  • 51
  • I will try this today sorry for the late reply I have had very little time to look at stack exchange I'll let you know today or tomorrow if it works, and if massive thanks to everyone by the way for being so kind and helping :) – Aeon Nov 16 '18 at 09:22
  • Okay so this code works but if I'm pasting from my website with white background I lose the center alignment and the cell line color and background color – Aeon Nov 16 '18 at 12:21
  • No problem, take your time, it is not race here :). Well try to use some clipboard viewer to see, what was actually copied to the clipboard, but the formating is prabably not part of the content but is rendered based on some css style. – Daniel Dušek Nov 16 '18 at 13:55
  • Ok the code works perfectly I just had commented the unit code accidentally made thank you very much – Aeon Nov 16 '18 at 14:07