Hyperlink i listebox?

  • Thread starter Thread starter Alen32
  • Start date Start date
A

Alen32

I got this code here which work well, but in column C I have hyperlink and
I would like to keep hyperlink function. How I can do that?
Private Sub UserForm_Initialize()
Dim cell As Range
With UserForm1
.ListBox1.RowSource = ""
.ListBox1.ColumnCount = 3
.ListBox1.Clear
End With
Set sh = Worksheets("Ark1")
For Each cell In sh.Range("A10:A250")
If cell.Value = sh.Range("A5").Value Then
With UserForm1
.ListBox1.AddItem cell.Value
.ListBox1.List(.ListBox1.ListCount - 1, 1) = cell.Offset(0,
2).Value
.ListBox1.List(.ListBox1.ListCount - 1, 2) = cell.Offset(0,
5).Value
End With
End If
Next

End Sub
 
I don't think you can keep the hyperlink, but maybe you can follow the link
yourself in code:

Option Explicit
Private Sub ListBox1_Change()
With Me.ListBox1
If .ListIndex > -1 Then
MsgBox .List(.ListIndex, 1)
ThisWorkbook.FollowHyperlink _
Address:="Http://" & .List(.ListIndex, 1)
End If
End With
End Sub

Private Sub UserForm_Initialize()
Dim cell As Range
Dim sh As Worksheet

With Me
.ListBox1.RowSource = ""
.ListBox1.ColumnCount = 3
.ListBox1.Clear
End With

Set sh = Worksheets("Ark1")

For Each cell In sh.Range("A10:A250")
If cell.Value = sh.Range("A5").Value Then
With UserForm1
.ListBox1.AddItem cell.Value
.ListBox1.List(.ListBox1.ListCount - 1, 1) _
= cell.Offset(0, 2).Value
.ListBox1.List(.ListBox1.ListCount - 1, 2) _
= cell.Offset(0, 5).Value
End With
End If
Next

End Sub

I added HTTP:// to my strings--you may not need this kind of adjustment.
 
It almost work. when I click on row in userform it activate one box with
hyperlink nd OK button but when I click on OK button comes errror mesage.
 
Back
Top