How to paste clipboard into Text Box 2

[tbl]

Can anyone help me with pasting the contents of the clipboard directly into an Excel Text Box (a shape)?
My VBA script copies the contents of a Wordpad text file successfully so that I have what I need to paste into Excel, but although I can manually select my Text Box and paste the text into it, I can't do it with VBA.

Richard

 

[jcrater]

how about activesheet.paste or using sendkeys?

 

[tbl]

Thanks for the reply.

activesheet.paste does not paste inside the text box unfortunately, and sendkeys is something that one tries to avoid if possible. The curious thing is that one can perform the operation manually just by pasting.

Richard

 

[TonyJollans]

Hi tbl,

ActiveSheet Properties and Methods do not refer to Controls on the Sheet - you must refer to the Controls more directly. Something like this should work ..

Sheet1.textbox1.Paste

Enjoy,
Tony

 

[CajunCenturion]

One way that you could try is to use the Clipboard API's. What follows copies the clipboard contents to a String buffer from which you can easily copy into the text box, or you might even be able to copy directly into the textbox.
In the declarations section of a code module, add the following declarations.

Public Const CF_TEXT = 1
Public Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Long, ByVal ByteLen As Long)

and in appropriate location in the code where you want the paste to happen, drop in the following code

Dim lLnd_TextHandle As Long
Dim lLng_TextLen As Long
Dim lStr_StrBuffer As String

OpenClipboard Me.hwnd
lLnd_TextHandle = GetClipboardData(CF_TEXT)
If (lLnd_TextHandle <> 0) Then
   lLng_TextLen = lstrlen(lLnd_TextHandle)
   If (lLng_TextLen > 0) Then
      lStr_StrBuffer = Space(lLng_TextLen)
      CopyMemory ByVal lStr_StrBuffer, ByVal lLnd_TextHandle, lLng_TextLen
      MsgBox lStr_StrBuffer
   End If
End If
CloseClipboard

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Loomah]

Tony
I was having a go at this and could now kick myself! It's faily easy within a form but that takes the biscuit!!

Have a purple pointy pip from me!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Thanks, Loomah.

Hope all's well on the home planet.

Enjoy,
Tony

 

[tbl]

Thanks very much Tony,
I had suspected that best answer would be also the simplest. The beauty of the simple paste is that it retains all the best of the formatting without showing the return characters

Richard

 

[CajunCenturion]

I agree that TonyJollans' approach is better. At the time I posted the API alternative, I was under the impression that the .Paste method wasn't working.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tbl]

Thanks CajunCenturion for taking the trouble to post. Using your approach I did arrive at the solution that I am cuurrently using.

Sub GetClipboard()
Dim clipboardContents As DataObject
Dim clipcontents As String, X As Integer

Set clipboardContents = New DataObject
clipboardContents.GetFromClipboard
clipcontents = ""
clipcontents = Replace(clipboardContents.GetText(1), Chr(13), "&quot

Set txt = ActiveSheet.TextBoxes("Text 7&quot
txt.Text = "" 'clear text box
txt.Text = clipcontents
NrChrs = Len(clipcontents)

For X = 0 To 20
If (X * 255 + 1) > NrChrs Then Exit Sub
txt.Characters(X * 255 + 1).Text = Mid(clipcontents, X * 255 + 1, 255)
Next X

End Sub

Richard