Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hyperlink question
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink Question | Excel Discussion (Misc queries) | |||
Another Hyperlink Question | Excel Discussion (Misc queries) | |||
Hyperlink question | Links and Linking in Excel | |||
Hyperlink question | Links and Linking in Excel | |||
VBA Hyperlink Question | Excel Programming |