Thank you very, very much. This is wonderful. I tried this and it worked!
I'll have to look at what I tried before to see why how this is different
from what I tried before; maybe because I didn't specify the ",1" parameter?
I thought that was the default. You know what they say about what we do
when we "assume" things :) I had tried using .SetText to copy a variable
even though the MS Help said it would only get data from a textbox.
If this info about these API's is available from Microsoft's web site, I
couldn't find it.
There's no use having a bunch of potentially useful addins if there is no
documentation that they exist, and how to use them.
I'm sure your answer will be of help to many people in the future when they
search this site to try to find out the same thing.


"NickHK" wrote:

Patricia,
You put text from anywhere to the clipboard with .SetText

Private Sub CommandButton2_Click()
Dim LongVal As Long
LongVal = 25
If SendToClipBoard(LongVal) = True Then ActiveSheet.Paste
End Sub

Function SendToClipBoard(AnyValue As Variant) As Boolean
Dim DatObj As DataObject

On Error GoTo Handler
Set DatObj = New DataObject
With DatObj
.SetText CStr(AnyValue), 1
.PutInClipboard
End With
SendToClipBoard = True
Exit Function
Handler:
SendToClipBoard = False

End Function

Or there is always the various Clipborad API, which all methods will resort
to in the end.
'Example from KPD-Team 1999, URL: http://www.allapi.net/

Const LR_LOADFROMFILE = &H10
Const IMAGE_BITMAP = 0
Const IMAGE_ICON = 1
Const IMAGE_CURSOR = 2
Const IMAGE_ENHMETAFILE = 3
Const CF_BITMAP = 2
Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal
hInst As Long, ByVal lpsz As String, ByVal dwImageType As Long, ByVal
dwDesiredWidth As Long, ByVal dwDesiredHeight As Long, ByVal dwFlags As
Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd 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
Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal
wFormat As Long) As Long
Private Sub Form_Load()
Dim hDC As Long, hBitmap As Long
'Load the bitmap into the memory
hBitmap = LoadImage(App.hInstance, "c:\windows\logow.sys", IMAGE_BITMAP,
320, 200, LR_LOADFROMFILE)
If hBitmap = 0 Then
MsgBox "There was an error while loading the bitmap"
Exit Sub
End If
'open the clipboard
OpenClipboard Me.hwnd
'Clear the clipboard
EmptyClipboard
'Put our bitmap onto the clipboard
SetClipboardData CF_BITMAP, hBitmap
'Check if there's a bitmap on the clipboard
If IsClipboardFormatAvailable(CF_BITMAP) = 0 Then
MsgBox "There was an error while pasting the bitmap to the clipboard!"
End If
'Close the clipboard
CloseClipboard
'Get the picture from the clipboard
Me.Picture = Clipboard.GetData(vbCFBitmap)
End Sub

NickHK

"Patricia Shannon" wrote in
message ...
After wasting a lot of time trying to find a straightforward way to do
this,
I created the following subroutine that opens a workbook, puts the
variable
(passed to the subroutine as an argument) in cell A1, copies that to the
clipboard, then closes the new workbook. With all the great things that
can
be done with VBA and Excel, I would think there would be an easier way to
do
this.

Public Sub CopyToClipboard(VariableToBeSaved)
' created by Patricia Shannon Aug. 22, 2006
' Copy variable to Clipboard

Workbooks.Add
Cells(1, 1) = VariableToBeSaved
Cells(1, 1).Copy
ActiveWorkbook.Close savechanges:=False

End Sub

In the subroutine where you need to save the variable, you would have
copytoclipboard(yourvariablehere)
"tomwashere2" wrote:

I would simply like to know how to specify that I would like to store a
variable value to the clipboard so that I can paste that information to
other
programs at my own discretion. Thanks!!!