Default Hyperlink in a cell which updates on text input?

trickz100

New Member
Joined
Dec 15, 2016
Messages
6
Hey all!

I'm very new in the excel world when it comes to formulas and codes etc, so please bare with me.

Is it possible to assign a web link to a specific cell, which can be updated with whatever text is inputed into that cell?

For example:

The cell has no text, but if I type "helloworld" into the cell, the link (not the text) changes to "www.google.com/helloworld" and of course, goes to that address when the text is clicked.

Many thanks in advance for your help! :)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi, welcome to the board!

Unless you are happy to use another column for the hyperlink then you will likely need VBA to do that - here is one VBA option.

To use:
1. Right Click the sheet tab where you want this to happen and choose "View Code"
2. Paste the below code into the code window on the right hand side.
3. Amend the URL
4. Amend the range of cells where you want this action to occur
5. Press ALT+Q to return to the worksheet
6. Save the workbook as a macro enabled workbook (*.XLSM)

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Const sURL = "https://www.google.com/search?8&q="
Dim r As Range, c As Range
Set r = Intersect(Range("A1:A100"), Target)
If Not r Is Nothing Then
    For Each c In r
        If c.Value <> "" Then
            c.Hyperlinks.Add c, sURL & c.Value, , , c.Value
                Else
            If c.Hyperlinks.Count > 0 Then c.Hyperlinks(1).Delete
        End If
    Next c
End If
End Sub
 
Upvote 0
Hi FormR!

Many thanks for your input, that code has worked a treat and is exactly what I was looking for! Thank you :)

Kind regards
 
Upvote 0
Hey, sorry to pick your brain again!

How do I go about assigning other cells with different URLs using the same method?

Cell F8 for example goes to google.com/helloworld

Whereas

Cell F10 goes to bing.com/helloworld

I seem to get Ambiguous errors etc.

Kind regards
 
Upvote 0
I seem to get Ambiguous errors etc.

Hi, you can only have one worksheet change event, so it's a case of putting all the logic into one piece of code.

How many different URL's are there? What is the range of cells where this needs to happen and is there any logic we can use to determine which URL is needed - for example is it always google for row 8 and bing for row 10?

Providing the details will help.
 
Upvote 0
Hello FormR,

My sincere apologies for the very late reply! I sidetracked for while before going back to the project and checking back on here!

I'm creating a simple form. Which just asks a few questions

Row 1: Which building are you in?
Row 2: Which floor are you on?
Row 3: What is your name? (Same URL with the name added to the end of it)
Row 4: What is your ID number (Another fixed URL with the ID at the end of it)

Thank you again for your help!

Kind regards
 
Upvote 0
Hi trickz100,

No worries, here is one way that I think will work for you:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const sURL_1 = "https://www.google.com/search?8&q="
Const sURL_2 = "https://www.bing.com/search?8&q="
Dim r As Range, c As Range
Set r = Intersect(Rows("3:4"), Target)
If Not r Is Nothing Then
    For Each c In r
        If c.Value <> "" Then
            c.Hyperlinks.Add c, IIf(c.Row = 3, sURL_1, sURL_2) & c.Value, , , c.Value
                Else
            If c.Hyperlinks.Count > 0 Then c.Hyperlinks(1).Delete
        End If
    Next c
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top