使用活动X控制按钮，将超链接从剪贴板粘贴到活动单元格上？

Question

我从剪贴板上粘贴超链接问题有问题。目的是使用一个活动X按钮粘贴剪贴板上的手/鼠标复制的超链接，该超链接位于我的工作表上的“活动单元格”中。该工作表受保护，因此按钮必须取消保护工作表，运行代码以粘贴剪贴板的超链接，然后保护工作表。在这个问题上的任何帮助将不胜感激。

基本思想：（我知道此代码不正确，只将其用作对话启动器）。

Private Sub CommandButton10_Click()
ActiveSheet.Unprotect Password:="Password1"
  Dim DataObj As MSForms.DataObject
    Set DataObj = New MSForms.DataObject
    DataObj.GetFromClipboard

    strPaste = DataObj.GetText(1)            <<<<certain something is missing after this line

    ActiveCell.Paste Link:=True
ActiveSheet.Protect Password:="Password1"
End Sub

Answer 1

MSFORM被弃用。使用

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .GetData("text")
        End Select
    End With
    End With
End Function

您这样称呼它：

Private Sub CommandButton10_Click()
    ActiveCell.Hyperlinks.Add ActiveCell, Clipboard
End Sub

---

在代码模块中，请准确添加以下所有行...

Private Sub CommandButton10_Click()
    Dim s$
    s = Clipboard
    If Len(s) Then
        ActiveSheet.Unprotect Password:="Password1"
        ActiveCell.Hyperlinks.Add ActiveCell, s
        ActiveSheet.Protect Password:="Password1"
    End If
End Sub

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .GetData("text")
        End Select
    End With
    End With
End Function

Answer 2

Private Sub CommandButton43_Click()
ActiveSheet.Unprotect Password:="Password1"
ActiveCell = Clipboard
Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .GetData("text")
        End Select
    End With
    End With
End Function
 ActiveSheet.Protect Password:="Password1"
End Sub