Excel VBA Listbox links

debugcn Published at Dev

HumanlyRespectable

I currently have a macro with two columns and many rows. The two columns holds info such as First name and a link to a folder or website.

When i click the button on the macro, it takes all the info from the excel sheet and shows the first name and places it in a listbox.

I was wondering, is it possible that when i click the button, i displays the first name in the listbox but also stores a link? when i select an item in the listbox, i want it to open up the link. is this possible?

i have thought of one way, and that is with the listbox and an array which stores the link, and when i click on an item, it searches the array and then opens the link, FOR EXMAPLE: if i click the first item in the listbox, it will go into the array and go to array(1) and then get that link.

That is one way i thought of but is there an easier way? rather than i storing the link into an array and all that.

the current code that i have is:

For row = 3 To 10
    ListBox1.AddItem Range("A" & row).Text
Next

i don't know how to add a hyperlink to this code

Dmitry Pavliv

Update:

What I would do is create Listbox with two columns:

Private Sub UserForm_Initialize()
    Dim row As Integer

    ListBox1.ColumnCount = 2
    ListBox1.ColumnWidths = "50;150"
    For row = 3 To 10
        ListBox1.AddItem Range("A" & row).Text
        ListBox1.List(ListBox1.ListCount - 1, 1) = Range("B" & row).Text
    Next
End Sub

Here is ListBox1_DblClick handler (when user double clicked on listbox item):

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    On Error GoTo ErrHandler
    ThisWorkbook.FollowHyperlink Address:=ListBox1.List(ListBox1.ListIndex, 1)
ExitHere:
    Exit Sub
ErrHandler:
    If Err.Number = -2147221014 Then
        MsgBox "Wrong link!"
    Else
        MsgBox "Error: " & Err.Description
    End If
    Resume ExitHere
End Sub

Then you can double click on any item in listbox to follow hyperlink:

enter image description here

Also I suggest you to change Range("A" & row).Text to ThisWorkbook.Worksheets("Sheet1").Range("A" & row).Text

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-26

Comments

0 comments

From Dev

Related Related

Article