ExcelBanter - Hyperlink question

Hi Greg,

Here's one solution. Not sure if it's the easiest way, but it seems to
work. This will display a button on your UserForm for each hyperlink on a
worksheet named "link". The button's caption will be the same as the
hyperlink's text to be displayed, and when clicked, the button will launch
your browser and navigate to the URL specified in the hyperlink.

First, create a class module named "cBtnSink" and add the following code to
it:

Public WithEvents btn As MSForms.CommandButton

Public URL As String

Private Sub btn_Click()
ThisWorkbook.FollowHyperlink URL
End Sub

Next, add the following code to your UserForm:

Private mcolBtn As Collection

Private Sub UserForm_Activate()
Dim hyp As Hyperlink
Dim lHypNum As Long
Dim sCtlName As String
Dim objBtn As cBtnSink
Dim lY As Long

Set mcolBtn = New Collection
lY = 1

For Each hyp In Worksheets("link").Hyperlinks
lHypNum = lHypNum + 1
sCtlName = "btnHyp" & CStr(lHypNum)
With Controls.Add("Forms.CommandButton.1", _
sCtlName, True)
.Object.Caption = hyp.TextToDisplay
.Top = lY
lY = lY + .Height + 2
Height = lY + .Height
Width = .Width
End With
Set objBtn = New cBtnSink
objBtn.URL = hyp.Address
Set objBtn.btn = Controls(sCtlName)
mcolBtn.Add objBtn
Next hyp

Set objBtn = Nothing
End Sub

Private Sub UserForm_Terminate()
Dim l As Long

For l = 1 To mcolBtn.Count
mcolBtn.Remove 1
Next l

Set mcolBtn = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Greg B wrote:
Hi all,

I have a worksheet with hyperlinks on it, I was wondering if it is
possible to have a userform with buttons on it when you press them to
activate these hyperlinks?

Something like this

on sheet link cell a2 I have a link called allergies

then on userform1 I want it to show the name allergies and show the
hyperlink.

I hope you can help

Thanks in advance

Greg