View Full Version : Solved: Hyperlink Cell to Include Cell Value in Link
Emoncada
06-19-2009, 06:31 AM
I don't know if this is possible, but I've seen some amazing things so this might be able to be done in vba.
I have UPS Tracking Numbers in a column and I would like to see if i can hyperlink the Cells with the Tracking Numbers and have the user click on it and automatically include it to this url. Tracking Numbers would be in a Range from E3: E500.
wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=(Here_the_cell_value_would_go)+&track.x=Track
This would automatically pull up UPS and track that package without the user needing to go and copy that cell then go to UPS and paste and Track.
Can this be done?
ukdane
06-19-2009, 06:39 AM
Something like this?
Dim Trackingnr as string
Trackingnr = ActiveCell.value
ActiveCell.Formula = "=HYPERLINK(""[wwwapps.ups.com/WebTracking/track? _
HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=("" & _
Trackingnr & "" +&track.x=Track"]"",""" & Trackingnr & """)"
Not tested.
Emoncada
06-19-2009, 06:47 AM
That doesn't seem to work but I think your on the right track.
ukdane
06-19-2009, 06:59 AM
I've just tested this, and it works.
Just run the code on the cell with the tracking nr.
Sub tracking()
Dim Trackingnr As String
Dim mytrace As String
Trackingnr = Trim(ActiveCell.Value)
mytrace = """http:\\wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=" & Trackingnr & "+&track.x=Track"""
ActiveCell.Formula = "=HYPERLINK(" & mytrace & ",""" & Trackingnr & """)"
End Sub
Emoncada
06-19-2009, 07:10 AM
Ok that seems to work only thing is, is there a way that I can have that macro hyperlink a range? so I don't have to go to each cell and run macro?
ukdane
06-19-2009, 11:51 AM
Emoncada: Do you mean one hyperlink for an entire range, or one hyperlink per cell for each cell in the range?
Is the range set (always known; for example A1:A13), or is it variable (eg: A1:A?) ?
Emoncada
06-19-2009, 11:53 AM
Yes one hyperlink per cell for each cell. Each cell will have a different tracking number unique to each row. So it would be like (D3 : D).
ukdane
06-19-2009, 12:16 PM
Sub tracking()
Dim Trackingnr As String
Dim mytrace As String
Dim mylastcell As Long
Dim activerow As Long
mylastcell = ActiveSheet.Range("A65536").End(xlUp).Row
activerow = 1 'replace 1 with the first row where the tracking number appears
Do While activerow < mylastcell + 1
Trackingnr = Trim(Range("A" & activerow).Value) 'replace "A" with the actual column
mytrace = """http:\\wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&trackNums=" & Trackingnr & "+&track.x=Track"""
Range("A" & activerow).Formula = "=HYPERLINK(" & mytrace & ",""" & Trackingnr & """)" 'replace "A" with the actual column
activerow = activerow + 1
Loop
End Sub
Job done!
Emoncada
06-19-2009, 12:56 PM
Seems to work is there anyway I can not have it hyperlink the first two rows? Which are column Headers.
Emoncada
06-19-2009, 12:57 PM
forget it saw your comment.
Emoncada
06-19-2009, 01:14 PM
Excellent ukdane!!!
Thanks Again.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.