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

Question

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

Excel Hero · Accepted Answer · 2022-05-04T22:39:24.920

4

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

edited May 04 '22 at 22:39

answered May 04 '22 at 07:28

Excel Hero

14,253
4
33
40

@DMatt Were you able to use this? – Excel Hero May 04 '22 at 19:14
So Far, I have not been able to get it to work. As I am only about 15 hours into my learning curve of VBA, I am not sure where to place the additional code you added in the "You call it like this" area of your reply. So far I have this down: – DMatt d20Gamer May 04 '22 at 20:26
I updated this answer to incorporate the HYPERLINK part. It should work as you expect now. – Excel Hero May 04 '22 at 21:20
Thank you very much. I was feeling I had already asked too much. – DMatt d20Gamer May 04 '22 at 21:25
I did and your solution worked perfectly. Thank you for helping me get this done. You have saved me many hours of frustration. – DMatt d20Gamer May 04 '22 at 21:29
You are most welcome. – Excel Hero May 04 '22 at 21:29
Excel Hero, I found a small yet possibly worrisome problem. If the clipboard is empty, when the Active X button is pressed, the code does not apply the "ActiveSheet.Protect Password:="Password1". This leaves the worksheet open to changes. I would like the worksheet to remain protected even if someone mistakenly clicks the Active X controll button with no information currently on the clipboard. Is there a different place in the code that the re-aplication of the protection can take place in order to avoid this loophole in the sheet's security? – DMatt d20Gamer May 04 '22 at 22:31
1

I'll adjust the code to account for that eventuality... – Excel Hero May 04 '22 at 22:36
1

Please try the updated ActiveX control procedure. It tests if the clipboard actually contains a value before unprotecting the worksheet. – Excel Hero May 04 '22 at 22:41
Perfect! Thank you. This alleviates the security concerns. – DMatt d20Gamer May 04 '22 at 22:59

score 0 · Answer 2 · answered May 04 '22 at 20:28

0

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

answered May 04 '22 at 20:28

DMatt d20Gamer

73
5

Place the Function `Clipboard()` outside and below the Sub code. – Excel Hero May 04 '22 at 20:30
Excel Hero, I am sure your code is good, I just need the last little bit of info to get it up and running... – DMatt d20Gamer May 04 '22 at 20:30
--Place the Function Clipboard() outside and below the Sub code. – Excel Hero Sorry, that is not something I understand. – DMatt d20Gamer May 04 '22 at 20:32
I cannot. I appreciate the offer to help in that capacity, however, that is not an option for me at this time. If you would be willing to be use newby lanuage on where to place the "Function Clipboard () outside the sub code" idea, I would appreciate it immensely. – DMatt d20Gamer May 04 '22 at 20:37
All of the code has to be placed in a code module in the VBA editor. So wherever you are placing your Command button code, just add the Clipboard function underneath. I will update my original answer to demonstrate... – Excel Hero May 04 '22 at 20:39
Thank you for the additional information and help. I appreciate your time and effort. – DMatt d20Gamer May 04 '22 at 20:41
The answer is updated now. – Excel Hero May 04 '22 at 20:43
That started working, however, it only pasted the words copied from the address bar of the internet. I was aiming for a full copy of an active hyperlink of the website when pasted. I probably did not inform you well enough about that part. The sheet I am working on remains locked to all who use it, but I am trying to add a button where they can copy paste links to the worksheet. Thank you for the help you gave. Sorry if I wasted your time. – DMatt d20Gamer May 04 '22 at 20:50
Looks like I need a few more steps included after the words are pasted. I would need it to right mouse click the selected cell, left click the Link option, paste the clipboard contents into the address bar and then left click the OK option. – DMatt d20Gamer May 04 '22 at 20:58

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

2 Answers2