How to paste clipboard into textbox sheet?

[HammerJoe]

Hi,

I was wondering how I can paste the contents of the clippboard into a
textbox that I have on the spreadsheet (object created with the
toolbox toolbar)

I have created a button called Paste that pastes the contents into the
textbox.
I have tried to create a macro, but the macro does not allow pasting
into the fieldbox, only on a cell.

Thanks

 

[Greg Wilson]

You need to set a reference to the Microsoft Forms 2.0 Object Library through
Tools>References in the VBE or declare dobj as Object instead.

Example code:

Sub Test()
Dim dobj As DataObject
Dim mydata As String

On Error GoTo ErrHandler
Set dobj = New DataObject
dobj.GetFromClipboard
mydata = dobj.GetText
With ActiveSheet.OLEObjects("TextBox1").Object
.Text = Replace(mydata, vbCrLf, "")
End With
On Error GoTo 0
Exit Sub
ErrHandler:
MsgBox "Clipboard is empty... ", vbExclamation, _
"Paste from Clipboard"
On Error GoTo 0
End Sub

Greg

 

[HammerJoe]

Hi greg,

Thanks so much for the help
I would never be able to come up with this one.
you woudl think excell would be easier to deal with clipboard
contents.

Thanks again.

 

[Leith Ross]

Hi greg,

Thanks so much for the help
I would never be able to come up with this one.
you woudl think excell would be easier to deal with clipboard
contents.

Thanks again.

Hello Joe,

If you think using the DataObject is complicated, here is how you
would have to do it using API calls...

'Written: January 31, 2008
'Author: Leith Ross
'Summary: Returns text from the clipboard as a string.


'Does the clipboard contain format?
Private Declare Function IsClipboardFormatAvailable _
Lib "User32.dll" _
(ByVal wFormat As Integer) As Long

'Open the clipboard
Private Declare Function OpenClipboard _
Lib "User32.dll" _
(ByVal hwnd As Long) As Long

'Get a pointer to the formatted data
Private Declare Function GetClipboardData _
Lib "User32.dll" _
(ByVal wFormat As Integer) As Long

'Close the clipboard
Private Declare Function CloseClipboard _
Lib "User32.dll" () As Long

'Finds first double Chr$(0) in a string
Private Declare Function lstrlen _
Lib "kernel32.dll" _
Alias "lstrlenA" _
(ByVal lpString As String) As Long

'Returns a pointer to the clipboard data in memory
Private Declare Function GlobalSize _
Lib "kernel32.dll" _
(ByVal hMem As Long) As Long

'Prevent the clipboard data from being overwritten
Private Declare Function GlobalLock _
Lib "kernel32.dll" _
(ByVal hMem As Long) As Long

'Free the memory used by the clipboard data
Private Declare Function GlobalUnlock _
Lib "kernel32" _
(ByVal hMem As Long) As Long

'Copy clipboard data from protected memory to a string buffer
Private Declare Sub MoveMemory _
Lib "kernel32" Alias "RtlMoveMemory" _
(ByVal strDest As Any, _
ByVal lpSource As Any, _
ByVal Length As Long)


Function GetClipboardText() As String

Dim DataSize As Long
Dim hClip As Long
Dim hData As Long
Dim pData As Long
Dim Ret As Long
Dim strText As String

Const CF_TEXT As Long = 1&

Ret = OpenClipboard(0&)
If Ret = 0 Then
MsgBox "Clipboard is in use."
Exit Function
End If

Ret = IsClipboardFormatAvailable(CF_TEXT)
If Ret Then
hData = GetClipboardData(CF_TEXT)
If hData Then
DataSize = GlobalSize(hData)
strText = Space$(DataSize)
pData = GlobalLock(hData)
Call MoveMemory(strText, pData, DataSize)
strText = Left$(strText, lstrlen(strText))
Call GlobalUnlock(hData)
End If
Else
MsgBox "There is No Text on the Clipboard."
End If

Ret = CloseClipboard
GetClipboardText = strText

End Function

Sincerely,
Leith Ross

 

[HammerJoe]

Hello Joe,

If you think using the DataObject is complicated, here is how you
would have to do it using API calls...

Ohh so much easier.
Now thats something I can read.
Thanks mate.