Covert web address to clickable link

[Pacman52]

Hi all could someone let me know if its possible to convert a web address entered on a userform to a clickable hyperlink.

So I have a basic data entry userform one of textboxes is for a website address . If the user types www.test.com, is there a way to convert this to clickable link so that when viewing the form the user can just click it and go to the site/page.

Thanks Paul

 

[Micron]

Since it's a data entry form I presume you've coded to add form data to a sheet. Then the accepted solution here should work. All you'd have to do is use the correct sheet/range reference to convert the string to a hyperlink.

However, it's not clear to me if you want to convert sheet data to hyperlinks as it's entered, or just follow what the user types in. If the latter, then any suitable event with this one line should follow the link. N.B - this alone would not prevent any malformed url from being attempted to follow.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ActiveWorkbook.FollowHyperlink Address:=TextBox1, NewWindow:=True

End Sub

 

[Pacman52]

Thanks so much for the reply - Yes the form data is coded to be added to the 'Data' sheet so the the thread you've kindly linked me too would be the best option.

Just one question though the user will not be able to view the Data sheet itself but can either view a particular selected record from a listbox on a different form or if they use the search button
the search sheet (which the user does see) is coded to filter out any records that don't match so I'm assuming that as the search sheet references the data sheet any websites would also be clickable from here as well?

Hopefully that makes sense !

Paul

 

[Micron]

Only if those links are in the search results? Not sure I completely grasp that. If the search result contains any cells that contain hyperlinks, I do believe they would be able to be followed from the search results form - unless you did something to prevent that, such as disable or lock the control. I presume that's a thing in Excel. I'm mainly versed in MS Access.

 

[Pacman52]

Yes it is for Excel. It not a major thing for any links to be Hyperlinks to be honest its more of a 'nicety' bit for info the search option and results are actually on a separate worksheet that references the main data worksheet where all the data is written to from the Userform.

I did manually add a new row on the data worksheet with a working hyperlink and then wet to the search sheet and the hyperlink was showing in the results correctly and clickable.

I fully appreciate you saying your mainly versed in Access but just in case anyone else reads this thread or you can answer it - I would love to be able firstly convert a textbox value to a hyperlink when a new record is created on the New Record Userform - after that I believe it will also show on any other userform that references the same record / row on the data worksheet.

 

[Micron]

Anything I've read on this is old (at least 2013) and all say you cannot. However, you can make a label caption equal what's in the textbox (use the exit event of the tb) and format the label as blue and underlined. It looks like a hyperlink. In the label click event put the code I wrote in post 2 (not sub/end sub lines). If user clicks on it, link will open if it's accessible. However, you won't get the hand cursor. Could get that with mousemove that calls a Windows API but that's probably not worth the work. Besides, then the width of the label will be an issue since mousemove will fire when the cursor goes over any portion of the label; even the far right where you would not normally see it.

 

[Pacman52]

Ironically I came across that same solution or one very similar whilst searching on the net last night. In principle it sounds pretty simple to implement so I give a test to see what happens. I'm not really bothered about the cursor aspect as with the text formatting it would be visually obvious its a link. I'll let you know how it works out.