从剪贴板获取值并将其合并到单元格值中

Question

下面的代码将剪贴板的内容赋值给变量s。打印出来，就会显示出来)然后，我试图将剪贴板内容合并到单元格值以及某个短语和当前日期。

结果我正在寻找单元格F775为"已计费发票-[最近的剪贴板内容]-在[今天的日期]"&;

问题:不确定在哪里把下面的代码-到一个模块或工作表?我试图使其工作与工作表之前右键单击事件单元格F775，下面的第二个代码，以便当F775右键单击其值成为上述结果。

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

在相应的工作表,现在我有两码的地方我知道下面的一个属,但通过剪贴板内容并不工作,如下我试图这样做。我只收到"已开票的发票"，日期是[今天]，两者之间没有[最近的剪贴板内容]。

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

Answer 1

像这样:

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

Answer 2

我是这样做的:

在工作表

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

在模块

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