Using Active X buttons to copy data to clipboard

[cr130]

Hello All,

Today I am trying to attach a VBA Code to a Active X button that will copy a certain text line to the clipboard. I know how to copy a cell with an active x button but wanted to do it without referencing a cell.

 

[DanteAmor]

Try:

Sub put_clipboard_1()
  'Check Microsoft Forms 2.0 Object Library in Tools / References
  Dim MyData As DataObject
  Set MyData = New MSForms.DataObject
 
  MyData.SetText "copy a certain text line to the clipboard"
  MyData.PutInClipboard
 
  'Example to paste on the active sheet
  ActiveSheet.Paste
 
End Sub

Or try:

Sub put_clipboard_2()
  Dim obj As Object
 
  Set obj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  obj.SetText "copy a certain text line to the clipboard"
  obj.PutInClipboard

  'Example to paste on the active sheet
  ActiveSheet.Paste

End Sub

 

[Artik]

It happens that the use of the DataObject clipboard fails. In this case, you can use another solution:

Private Sub Test()
    Dim var1
    Dim var2

    var1 = "Excel Hero was here."
    var2 = 123

    'send to clipboard
    Clipboard var1 & vbLf & var2

    'read from clipboard
    MsgBox Clipboard
End Sub


Function Clipboard(Optional s As String) As String
'This is a solution that does NOT use MS forms or the Win32 API.
'Instead, it uses the Microsoft HTML Object Library, which is fast and ubiquitous.

    Dim v As Variant

    v = s  'Send to variant to support 64-bit VBA.

    With CreateObject("htmlfile")
        With .parentWindow.clipboardData
            Select Case True
            Case Len(s) > 0: .setData "text", v
            Case Else: Clipboard = .GetData("text")
            End Select
        End With
    End With

End Function

Artik

 

[cr130]

Try:

Sub put_clipboard_1()
  'Check Microsoft Forms 2.0 Object Library in Tools / References
  Dim MyData As DataObject
  Set MyData = New MSForms.DataObject
 
  MyData.SetText "copy a certain text line to the clipboard"
  MyData.PutInClipboard
 
  'Example to paste on the active sheet
  ActiveSheet.Paste
 
End Sub

Or try:

Sub put_clipboard_2()
  Dim obj As Object
 
  Set obj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  obj.SetText "copy a certain text line to the clipboard"
  obj.PutInClipboard

  'Example to paste on the active sheet
  ActiveSheet.Paste

End Sub

Thank you for responding, it didnt seem to work

 

[cr130]

It happens that the use of the DataObject clipboard fails. In this case, you can use another solution:

Private Sub Test()
    Dim var1
    Dim var2

    var1 = "Excel Hero was here."
    var2 = 123

    'send to clipboard
    Clipboard var1 & vbLf & var2

    'read from clipboard
    MsgBox Clipboard
End Sub


Function Clipboard(Optional s As String) As String
'This is a solution that does NOT use MS forms or the Win32 API.
'Instead, it uses the Microsoft HTML Object Library, which is fast and ubiquitous.

    Dim v As Variant

    v = s  'Send to variant to support 64-bit VBA.

    With CreateObject("htmlfile")
        With .parentWindow.clipboardData
            Select Case True
            Case Len(s) > 0: .setData "text", v
            Case Else: Clipboard = .GetData("text")
            End Select
        End With
    End With

End Function

Artik

works perfect with some edits, thank you!

 

[Artik]

Thank you for responding, it didnt seem to work

Describe what problem you are having using DataObject. This information is quite important to us for the future.

 

[cr130]

Describe what problem you are having using DataObject. This information is quite important to us for the future.

It may be my issue but i didnt want it to paste just copy to the clipboard so i could paste in another program.

 

[Artik]

But what are the symptoms of the code "not working"? Are you getting some kind of error? Is it just that there is no data in the clipboard and there is nothing to paste?

Did you add a reference to the 'Microsoft Forms 2.0 Object Library' before running the put_clipboard_1 code? If you use the put_clipboard_2 procedure, you don't need to add a reference.

Artik

 

[cr130]

no error, it just paste into the cell i am highlighted in excel. yes, i did have the object library selected.

 

[DanteAmor]

no error, it just paste into the cell i am highlighted in excel. yes, i did have the object library selected.

In the macro I put a comment:

"'Example to paste on the active sheet"

It is just an example to paste what you put in memory.

To make it work, just delete this line:

ActiveSheet.Paste

Sub put_clipboard_2()
  Dim obj As Object
 
  Set obj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  obj.SetText "copy a certain text line to the clipboard"
  obj.PutInClipboard
End Sub