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! :)
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
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
 

trickz100

New Member
Joined
Dec 15, 2016
Messages
6
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
 

trickz100

New Member
Joined
Dec 15, 2016
Messages
6
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
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

trickz100

New Member
Joined
Dec 15, 2016
Messages
6
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
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,071
Latest member
hijackhippo

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
Top