"Linkize" a text box or label

If I have a text box or label of web URLs, Is it possible to turn these
URLs into links the user can then click on?

Thanks

Hi Rishi,

Yes, it's a pretty simple matter to turn a text box that contains text that
looks like a hyperlink (i.e., blue-coloured and underlined) into an actual
hyperlink. Place focus on any edge of your text box - you'll see a four-way
arrow - and right-click. You'll see the choice, "Hyperlink" in the
right-click menu. Choose that one then click on the button on the left-hand
side of the "Insert Hyperlink" dialogue that says, "Existing File or Web
Page". Then in the "Address" bar in the lower middle part of the dialogue,
enter the URL (e.g., www.monkeyboy.com). Then click OK. Incidentally, you
can apply this approach to just about any drawing object (even clip art
images) and, even if you choose a text box, the contained text doesn't even
have to look like the URL - you could instead use simple text like, "Click
here to see the monkeyboy.com website" (because you actually specify the URL
address for the link in the "Insert Hyperlink" dialogue). Hope this helps.

Paul

"Rishi Dhupar" wrote:

> If I have a text box or label of web URLs, Is it possible to turn these
> URLs into links the user can then click on?
>
> Thanks
>
>

Not exactly what I was looking for, but thanks.

I found this http://www.j-walk.com/ss/excel/tips/tip71.htm

Which helped some, but I am using a text box that has 1-4 different
URLs each on seperate lines. Anyway to get each of the lines to become
a URL?

Thanks

Hi again Rishi,
Sorry I misunderstood your original posting. I guess you were referring to
text box controls in a VBA form not a text box in a spreadsheet. Is it
possible for you to use a Listbox control instead of a Text box control on
your form to contain the list of URLS? You can populate the listbox with the
desired URLs fairly easily:

If there are only several URLS to choose from, just use the list box's
additem method, for example:

Private Sub UserForm_Initialize()
ListBox1.AddItem "http://www.cnn.com"
ListBox1.AddItem "http://www.nytimes.com"
End Sub

If you may have a larger number of URLs that you might want to change from
time to time, you could put them in a range of cells right in your
spreadsheet and reference that range in the list box control by setting the
"RowSource" property of the list box to the range (e.g. if the two URLs above
were in the range A1 to A2 that you've named, "URLs" on Sheet1, you could set
RowSource to Sheet1!URLs).

Private Sub UserForm_Initialize()
ListBox1.RowSource = Range("URLs").Address
End Sub

Now, you want to open the website of whichever URL in the list box is
selected. Choose which list box event you would prefer to make this occur
(either a single-click on the URL item or a double-click). Let's say you
choose the single-click event. You'll just do a simple extension to John
Walkenbach's link code:

Private Sub ListBox1_Click()
Dim i As Integer
i = ListBox1.ListIndex + 1
Call OpenWebsite(i)
End Sub

Private Sub OpenWebsite(i As Integer)
Dim Link As String
Link = Range("URLs").Cells(i, 1).Value
On Error GoTo NoCanDo
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Unload Me
Exit Sub
NoCanDo:
MsgBox "Cannot open " & Link
End Sub

Hope this was what you were looking for Rishi.

Paul


"Rishi Dhupar" wrote:

> Not exactly what I was looking for, but thanks.
>
> I found this http://www.j-walk.com/ss/excel/tips/tip71.htm
>
> Which helped some, but I am using a text box that has 1-4 different
> URLs each on seperate lines. Anyway to get each of the lines to become
> a URL?
>
> Thanks
>
>

Rishi, I should note that an alternative approach to using the listbox index
is to use the actual string selected in the listbox (the listbox value) which
is very similar to but perhaps a bit more readable than the code in my
previous post (either one produces the same result):

Private Sub ListBox1_Click()
Dim sURL As String
sURL = ListBox1.Value
Call OpenWebsite(sURL)
End Sub

Private Sub OpenWebsite(sURL As String)
Dim Link As String
Link = sURL
On Error GoTo NoCanDo
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Unload Me
Exit Sub
NoCanDo:
MsgBox "Cannot open " & Link
End Sub

"Paul Mathews" wrote:

> Hi again Rishi,
> Sorry I misunderstood your original posting. I guess you were referring to
> text box controls in a VBA form not a text box in a spreadsheet. Is it
> possible for you to use a Listbox control instead of a Text box control on
> your form to contain the list of URLS? You can populate the listbox with the
> desired URLs fairly easily:
>
> If there are only several URLS to choose from, just use the list box's
> additem method, for example:
>
> Private Sub UserForm_Initialize()
> ListBox1.AddItem "http://www.cnn.com"
> ListBox1.AddItem "http://www.nytimes.com"
> End Sub
>
> If you may have a larger number of URLs that you might want to change from
> time to time, you could put them in a range of cells right in your
> spreadsheet and reference that range in the list box control by setting the
> "RowSource" property of the list box to the range (e.g. if the two URLs above
> were in the range A1 to A2 that you've named, "URLs" on Sheet1, you could set
> RowSource to Sheet1!URLs).
>
> Private Sub UserForm_Initialize()
> ListBox1.RowSource = Range("URLs").Address
> End Sub
>
> Now, you want to open the website of whichever URL in the list box is
> selected. Choose which list box event you would prefer to make this occur
> (either a single-click on the URL item or a double-click). Let's say you
> choose the single-click event. You'll just do a simple extension to John
> Walkenbach's link code:
>
> Private Sub ListBox1_Click()
> Dim i As Integer
> i = ListBox1.ListIndex + 1
> Call OpenWebsite(i)
> End Sub
>
> Private Sub OpenWebsite(i As Integer)
> Dim Link As String
> Link = Range("URLs").Cells(i, 1).Value
> On Error GoTo NoCanDo
> ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
> Unload Me
> Exit Sub
> NoCanDo:
> MsgBox "Cannot open " & Link
> End Sub
>
> Hope this was what you were looking for Rishi.
>
> Paul
>
>
> "Rishi Dhupar" wrote:
>
> > Not exactly what I was looking for, but thanks.
> >
> > I found this http://www.j-walk.com/ss/excel/tips/tip71.htm
> >
> > Which helped some, but I am using a text box that has 1-4 different
> > URLs each on seperate lines. Anyway to get each of the lines to become
> > a URL?
> >
> > Thanks
> >
> >

Brilliant, didn't think of using a list box.

Thanks a bunch.