Hi,

I wonder if you can put some predefined text on the clipboard (not the
contents of a cell - dashed line) but a text like you should copy in any
program (and like you can copy a part of text in the formulabar of a whole
line).

I'd like to paste it back when I'm inputting in a cell when the user needs
it (ctrl-v works then).

thanks
Jos Vens

"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not the
: contents of a cell - dashed line) but a text like you should copy in any
: program (and like you can copy a part of text in the formulabar of a whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the user needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools reference in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)
Paul D

Hi Paul,

Thanks for your reply. I guess that is exactly what I want, but it has to
work without extra references. Maybe It can work with an API-call - that
would be OK! But, in VBA, I guess it is not possible without making
references to new libraries, and I don't want to do that.

Thanks again,
Jos Vens

"PaulD" <nospam schreef in bericht
...
"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not the
: contents of a cell - dashed line) but a text like you should copy in any
: program (and like you can copy a part of text in the formulabar of a
whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the user
needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)
Paul D

Hi Jos

There are libraries and there are libraries. Just insert a blank userform in
the workbook and the reference is there without further actions.

HTH. Best wishes Harald

"Jos Vens" skrev i melding
...
Hi Paul,

Thanks for your reply. I guess that is exactly what I want, but it has to
work without extra references. Maybe It can work with an API-call - that
would be OK! But, in VBA, I guess it is not possible without making
references to new libraries, and I don't want to do that.

Thanks again,
Jos Vens

"PaulD" <nospam schreef in bericht
...
"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not the
: contents of a cell - dashed line) but a text like you should copy in
any
: program (and like you can copy a part of text in the formulabar of a
whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the user
needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools
reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you
can
change to late bind (i.e. use CreateObject)
Paul D

Hi Harald,

I know that, but what if a library is not on the local machine of the user?
I can provide it in my setup but if I work on drive D (let's say, I put the
..dll which is the referenced library in folder D:\Program\MyRef.dll) and the
user installs on C:\ (lets say in folder C:\Program\MyRef.dll), will it stil
work or will he get an error message on startup? I mean, is the path (which
you can link on the blank userform absolute or relative?)

Jos

"Harald Staff" schreef in bericht
...
Hi Jos

There are libraries and there are libraries. Just insert a blank userform
in
the workbook and the reference is there without further actions.

HTH. Best wishes Harald

"Jos Vens" skrev i melding
...
Hi Paul,

Thanks for your reply. I guess that is exactly what I want, but it has to
work without extra references. Maybe It can work with an API-call -
that
would be OK! But, in VBA, I guess it is not possible without making
references to new libraries, and I don't want to do that.

Thanks again,
Jos Vens

"PaulD" <nospam schreef in bericht
...
"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not
the
: contents of a cell - dashed line) but a text like you should copy in
any
: program (and like you can copy a part of text in the formulabar of a
whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the user
needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools
reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you
can
change to late bind (i.e. use CreateObject)
Paul D

Any installation of Excel (= version 97) can open an excel file that
contains a userform. It's there with Excel. So it /will/ be on the local
machine, that's the point. Forget about references and what libraries really
are. Just insert a blank userform in the file, put Paul's code in it too and
worry no more.

HTH. Best wishes Harald

"Jos Vens" skrev i melding
...
Hi Harald,

I know that, but what if a library is not on the local machine of the
user?
I can provide it in my setup but if I work on drive D (let's say, I put
the
.dll which is the referenced library in folder D:\Program\MyRef.dll) and
the
user installs on C:\ (lets say in folder C:\Program\MyRef.dll), will it
stil
work or will he get an error message on startup? I mean, is the path
(which
you can link on the blank userform absolute or relative?)

Jos

"Harald Staff" schreef in bericht
...
Hi Jos

There are libraries and there are libraries. Just insert a blank
userform
in
the workbook and the reference is there without further actions.

HTH. Best wishes Harald

"Jos Vens" skrev i melding
...
Hi Paul,

Thanks for your reply. I guess that is exactly what I want, but it has
to
work without extra references. Maybe It can work with an API-call -
that
would be OK! But, in VBA, I guess it is not possible without making
references to new libraries, and I don't want to do that.

Thanks again,
Jos Vens

"PaulD" <nospam schreef in bericht
...
"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not
the
: contents of a cell - dashed line) but a text like you should copy
in
any
: program (and like you can copy a part of text in the formulabar of
a
whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the
user
needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools
reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you
can
change to late bind (i.e. use CreateObject)
Paul D

Grüezi Paul

PaulD schrieb am 09.02.2005

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools reference in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)

Do you have an excple how to use latebinding with the DataObject?
I was not able to run it this way, but maybe I used the wrong statements.



Regards
Thomas Ramel

--
- MVP for Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

"Thomas Ramel" wrote in message
...
: Grüezi Paul
:
: PaulD schrieb am 09.02.2005
:
: Sub Macro1()
: Dim MyData As DataObject
: Set MyData = New DataObject
: MyData.SetText "Enter what you want on the clipboard here"
: MyData.PutInClipboard
: End Sub
:
: In order to run since MyData is earlybind, you must go to tools
reference in
: the VBA editor and select Microsoft Forms 2.0 Object Library. Or you
can
: change to late bind (i.e. use CreateObject)
:
: Do you have an excple how to use latebinding with the DataObject?
: I was not able to run it this way, but maybe I used the wrong statements.

Thomas,
You appear to be correct. I made the assumption (bad Paul) that because it
was an object, late binding would be possible. After further review, I see
this is not as easy as it appeared. I would imagine code could be used to
add the reference but then you would need to check the trust access to
Visual Basic Project (doh!). Unfortunately I will be out town till Tuesday
so I can't research anymore, but I will check when I get back
Paul D

Option Explicit

Public Const GHND = &H42
Public Const CF_TEXT = 1

Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal _
dwBytes
As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As
Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) _
As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As _
Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
As Long, ByVal hMem
As Long) As Long


Public Function ClipBoard_SetText(strCopyString As String) As Boolean

Dim hGlobalMemory As Long
Dim lpGlobalMemory As Long
Dim hClipMemory As Long

'this is an example of how this works
'------------------------------------
'Dim strString As String
'strString = "test"
'ClipBoard_SetText strString
'------------------------------------

'Allocate moveable global memory
'-------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(strCopyString) + 1)

'Lock the block to get a far pointer to this memory
'--------------------------------------------------
lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory
'-------------------------------------
lpGlobalMemory = lstrcpy(lpGlobalMemory, strCopyString)

'Unlock the memory and then copy to the clipboard
'------------------------------------------------
If GlobalUnlock(hGlobalMemory) = 0 Then
If OpenClipboard(0&) < 0 Then
Call EmptyClipboard
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
ClipBoard_SetText = CBool(CloseClipboard)
End If
End If

End Function

Function ClipBoard_GetText() As String

Dim hClipMemory As Long
Dim lpClipMemory As Long
Dim strCBText As String
Dim retval As Long
Dim lngSize As Long

If OpenClipboard(0&) < 0 Then
'Obtain the handle to the global
'memory block that is referencing the text
'----------------------------------------
hClipMemory = GetClipboardData(CF_TEXT)
If hClipMemory < 0 Then
'Lock Clipboard memory so we can
'reference the actual data string
'--------------------------------
lpClipMemory = GlobalLock(hClipMemory)
If lpClipMemory < 0 Then
lngSize = GlobalSize(lpClipMemory)
strCBText = Space$(lngSize)
retval = lstrcpy(strCBText, lpClipMemory)
retval = GlobalUnlock(hClipMemory)
'Peel off the null terminating character
'---------------------------------------
strCBText = Left(strCBText, InStr(1, strCBText, Chr$(0),
0) - 1)
Else
MsgBox "Could not lock memory to copy string from."
End If
End If
Call CloseClipboard
End If

ClipBoard_GetText = strCBText

End Function


Sub tester()

Dim strTest As String

ClipBoard_SetText "This is a clipboard test"

strTest = ClipBoard_GetText

End Sub


Can't remember where I got it from, but I know it works perfect.
Works all with API, so no extra libraries needed.

RBS


"PaulD" <nospam wrote in message
...
"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not the
: contents of a cell - dashed line) but a text like you should copy in any
: program (and like you can copy a part of text in the formulabar of a
whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the user
needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)
Paul D

Hi Bart,

it looks like very much trouble to let it run, but also for me, it works
fine!

Thank you very much!
Jos

"RB Smissaert" schreef in bericht
...
Option Explicit

Public Const GHND = &H42
Public Const CF_TEXT = 1

Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
_
dwBytes
As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As
Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long)
_
As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) _
As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As _
Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
As Long, ByVal hMem
As Long) As Long


Public Function ClipBoard_SetText(strCopyString As String) As Boolean

Dim hGlobalMemory As Long
Dim lpGlobalMemory As Long
Dim hClipMemory As Long

'this is an example of how this works
'------------------------------------
'Dim strString As String
'strString = "test"
'ClipBoard_SetText strString
'------------------------------------

'Allocate moveable global memory
'-------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(strCopyString) + 1)

'Lock the block to get a far pointer to this memory
'--------------------------------------------------
lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory
'-------------------------------------
lpGlobalMemory = lstrcpy(lpGlobalMemory, strCopyString)

'Unlock the memory and then copy to the clipboard
'------------------------------------------------
If GlobalUnlock(hGlobalMemory) = 0 Then
If OpenClipboard(0&) < 0 Then
Call EmptyClipboard
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
ClipBoard_SetText = CBool(CloseClipboard)
End If
End If

End Function

Function ClipBoard_GetText() As String

Dim hClipMemory As Long
Dim lpClipMemory As Long
Dim strCBText As String
Dim retval As Long
Dim lngSize As Long

If OpenClipboard(0&) < 0 Then
'Obtain the handle to the global
'memory block that is referencing the text
'----------------------------------------
hClipMemory = GetClipboardData(CF_TEXT)
If hClipMemory < 0 Then
'Lock Clipboard memory so we can
'reference the actual data string
'--------------------------------
lpClipMemory = GlobalLock(hClipMemory)
If lpClipMemory < 0 Then
lngSize = GlobalSize(lpClipMemory)
strCBText = Space$(lngSize)
retval = lstrcpy(strCBText, lpClipMemory)
retval = GlobalUnlock(hClipMemory)
'Peel off the null terminating character
'---------------------------------------
strCBText = Left(strCBText, InStr(1, strCBText, Chr$(0),
0) - 1)
Else
MsgBox "Could not lock memory to copy string from."
End If
End If
Call CloseClipboard
End If

ClipBoard_GetText = strCBText

End Function


Sub tester()

Dim strTest As String

ClipBoard_SetText "This is a clipboard test"

strTest = ClipBoard_GetText

End Sub


Can't remember where I got it from, but I know it works perfect.
Works all with API, so no extra libraries needed.

RBS


"PaulD" <nospam wrote in message
...
"Jos Vens" wrote in message
...
: Hi,
:
: I wonder if you can put some predefined text on the clipboard (not the
: contents of a cell - dashed line) but a text like you should copy in
any
: program (and like you can copy a part of text in the formulabar of a
whole
: line).
:
: I'd like to paste it back when I'm inputting in a cell when the user
needs
: it (ctrl-v works then).
:
: thanks
: Jos Vens
:
Not sure I fully understand, are you looking for something like this?

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText "Enter what you want on the clipboard here"
MyData.PutInClipboard
End Sub

In order to run since MyData is earlybind, you must go to tools reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)
Paul D

Hi Jos,
You can use Internet Explorer object:

Sub PutInClipBoard()
ClipBoardText = InputBox("Enter some text:" _
, "Test Clip Write / Read" _
, "Enter what you want on the clipboard here")
With CreateObject("InternetExplorer.Application")
..Navigate "about:blank"
..document.ParentWindow.ClipboardData _
..SetData "text", ClipBoardText
..Quit
End With
Cells(1, 1).Select
ActiveSheet.Paste
End Sub

Regards,
MP

"Jos Vens" a écrit dans le message de
...
Hi,

I wonder if you can put some predefined text on the clipboard (not the
contents of a cell - dashed line) but a text like you should copy in any
program (and like you can copy a part of text in the formulabar of a whole
line).

I'd like to paste it back when I'm inputting in a cell when the user needs
it (ctrl-v works then).

thanks
Jos Vens