Associating Unique Variables To HyperLinks In Cell Range

[BrI]

I'm setting up a spreadsheet that will hold "fake hyperlinks" that will actually just act as command buttons to call a macro - no problem with this hyperlink code per se.

The hyperlinks will be created using a command button within a range like A1:C15 - will not always be fully populated. A string on the clipboard will also be stored as a variable when each hyperlink is created, need a different / unique variable for each link.

When the hyperlink is clicked (calling the macro) I need to get the unique variable value that was associated with it when it was created as this value will be used in the macro.

I'm struggling with how to associate unique variables to each hyperlink so they can be retrieved when the link is clicked. How should I do this?

[Leith Ross]

Hello BrI,

Store them in a Public (Module level) Collection object.

[BrI]

Thanks, I will read up on collections as not very familiar, any general examples would be great

[Leith Ross]

Hello BrI,

A Collection object stores key/item pairs. The key is a string used to reference the item stored under the key. An example of this is Worksheets. Each Worksheet has a name (the key) and the Worksheet Object (the item associated with the key). While key must be a string, the item can be any number, string, or object except a User Defined Data Type.

I am not exactly sure how you have your Hyperlinks setup, but I will use a real Hyperlink example for this Collection. For this example we will assume there are real web hyperlinks in column "A". These hyperlinks must be pasted in or automatically converted when type in by Excel. This will not work with Hyperlinks created by Formulas.

PLace Code in VBA Module

' Making this Public in Module makes the Collection available to all code in your VBA Project.
Public myColl As Collection

Sub LoadCollection()

    Dim Cell As Range
    Dim Rng As Range

        ' Check if the Collection object is instantiated.
        If myColl Is Nothing Then
            Set myColl = New Collection
        End If

        Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))

        For Each Cell In Rng
            ' If a key already exists then an error occurs. Ignore the error.
            ' Key names ignore case.
            On Error Resume Next
                If Cell.HyperLinks.Count > 0 Then
                    myColl.Add Cell.Hyperlinks(1), Cell.Text
                End If
            On Error GoTo 0
        Next Cell
End Sub

Let's assume in cell "A1", you type "www.google.com". Excel will convert this to a hyperlink. When you access a member of the Collection by it's key name, in this "www.google.com", the Hyperlink object is returned. You now have full access to all properties and methods associated with this object.

Sub Test()
 
    Dim Hlnk As Object

        ' Assign the hyperlink for the key to an object variable
        Set Hlnk = myColl("www.google.com")
        
        ' Open the web page in the browser.
        Hlnk.Follow

End Sub

[BrI]

This is excellent, really appreciate it. I will be able get it going now, the key/item pairing idea provides a simple solution to a hard prob.

[Leith Ross]

Hello BrI,

You're welcome. Glad I could help.