Paste a hyperlink to the active cell, from the clipboard, using an Active X control button?-CodePudding

I am having issues with pasting a hyperlink from my clipboard. The goal is to use an Active X button to paste a hand/mouse copied hyperlink that is sitting on the clipboard into the "Active Cell" on my worksheet. The worksheet is protected, so the button must unprotect the sheet, run the code to paste the hyperlink from the clipboard, then protect the sheet. Any help on this issue would be greatly appreciated.

Basic Idea: (I know this code is not correct, only using it as a conversation starter).

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

CodePudding user response：

MSForms is deprecated. Use this function instead:

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

You call it like this:

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

In a code module please add all of the following lines exactly...

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

CodePudding user response：

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