HyperLinks Cell Content Automation

[kkorp]

I have a spreadsheet that we tracks our dell computers in. I have use the first column for the service tag. I would like to be able to have the sheet automatically create a hyperlink base off the content typed in the service tag column/cell and assign the service tag as the display text in the same cell and/or when you enter a new service tag. It would helpful to the others that use the spreadsheet when they need to look up anything about a specific Dell PC/Laptop.

Below is the URL that would point to the dell site.
The red text is the Service Tag Number passed from the cell.
This URL is Hyperlinked to the content of the cell and the display text is the same as the content of the Service Tag text. Also if the cell is empty don't create URL Hyperlink.

Product Support | Dell US

######/overview

P.S. is the possible

 

[Candyman8019]

Ah, okay...before:
Application.EnableEvents = False
Add the following line:

if target.value = "" then exit sub

 

[kkorp]

Trying to attach hyperlink to text not cell, is this possible.

Help?

 

[Candyman8019]

Yes, that's the way this script will work. To clarify, if you have a row that is 3 times larger than a normal row...the user must click the text in the cell...not the white space in the cell.

 

[kkorp]

Thanks for the input, is there a way to modify the code to convert the cells that have content in the before the code was added.

any thoughts

Thanks

 

[Candyman8019]

For sure. Give this simple loop a try. Make sure your code in this loop is correct and update the range to match what you want updated...

Private Sub RunOneTime()
Dim ncell As Range
Dim rng As Range
dim tempval as string


Set rng = ActiveSheet.Range("C9:C12")
For Each ncell In rng
tempval = ncell.value
  if ncell.value <>"" then
      ncell.Formula =  "=HYPERLINK(""[URL='https://www.dell.com/support/home/en-us/product-support/servicetag/']Product Support | Dell US[/URL]" & tempval & "/overview"",""" & tempval & """)"
 end if

Next ncell
End Sub