Get value from clipboard & incorporate it into a cell value-CodePudding

The code below assigns contents of clipboard to a variable S. (When running Debug.Print it shows up) Then I am trying to incorporate the clipboard contents into a cell value along with a certain phrase and current date.

Result I'm seeking for cell F775 to be "Billed invoice - [most recent clipboard content] - on [todays date]"

Problems: Not sure where to put the below code -- into a Module or Worksheet? I am trying to make it work with Worksheet before right click event onto cell F775, second code below, so that when F775 right clicked on its value becomes the result described above.

Public Clipboard As New MSForms.DataObject
Sub readClipboard()
'Tools -> References -> Microsoft Forms 2.0 Object Library <<DONE
'or you will get a "Compile error: user-defined type not defined"
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
'Debug.Print S 'print code in the Intermediate box in the Macro editor
End Sub

For now I have both codes in corresponding worksheet, where I know below one belongs, but passing the clipboard contents does NOT work, as I'm trying to do it below. I just get "Billed invoice - on [todays date]", without the [most recent clipboard content] between them.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

'if r.clcik in Range, then context menu is canceled and range populates
If Not Application.Intersect(Target, Range("F775")) Is Nothing Then
Range("F775").Value = "Billed invoice - " & S & " on " & Format(Now, "MM.dd.yy")
End If
'if r.clcik in Range, then context menu is canceled and selected cell is copied.
' If Not Application.Intersect(Target, Range("b1:u8")) Is Nothing Then
Cancel = True
Selection.Copy
' End If
End Sub

CodePudding user response：

Like this:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    'if r.click in Range, then context menu is canceled and range populates
    If Not Application.Intersect(Target, Me.Range("F775")) Is Nothing Then
        Target.Value = "Billed invoice - " & ClipboardText() & " on " & Format(Now, "MM.dd.yy")
        Cancel = True
    End If
    'if r.clcik in Range, then context menu is canceled and selected cell is copied.
    If Not Application.Intersect(Target, Me.Range("B1:U8")) Is Nothing Then
        Cancel = True
        Selection.Copy
    End If
End Sub

Function ClipboardText()
    With New MSForms.DataObject
        .GetFromClipboard
        ClipboardText = .GetText
    End With
End Function

CodePudding user response：

Here's how I made it work:

In Worksheet

Private Sub Worksheet_BeforeRightClick(ByVal Target As 
Range, Cancel As Boolean)

If Not Application.Intersect(Target, Range("F775")) Is Nothing Then

Call readClipboard

End If
' If Not Application.Intersect(Target, Range("b1:u8")) Is Nothing Then
Cancel = True
Selection.Copy
'Cancel = False
' End If
End Sub

In Module

Public Clipboard As New MSForms.DataObject
Sub readClipboard()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'if you will get a "Compile error: user-defined type not defined"
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
'Debug.Print S 'print code in the Intermediate box in the Macro editor
Range("F775").Value = "Billed invoice - " & S & " on " & Format(Now, "MM.dd.yy")
End Sub