VBA Cell Reference + hyperlink

[JustinLock]

Hi all,

I need to reference data in another cell as part of a hyperlink.
The data is part of a url but would change depending on the users input so I can't just use a standard static address.

For example, something that would take the standard google url and use the word "mail" from another cell or if the user inputs "drive" VBA would place that word at the end of the url and run it as a standard hyperlink

 

[Legacy 143009]

How about this one?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        ActiveSheet.Hyperlinks.Add Range("A2"), Address:="https://www.google.com/" & KeyCells.Value & "/"

End If
End Sub

 

[JustinLock]

Unfortunately not. The cell being referenced is in another sheet and the code doesn't show as a selection option when assigning a macro to a button, which I assume is down to it being a private sub rather than standard.
Here's what I have so far.

Sub RoutePlanning()
    Sheet7.Unprotect "DHH2fanclub"
    Sheet7.Activate
        Sheet7.Range("AF1").Select
            Selection.Copy
 Dim chromeFileLocation As String
  Dim hyperlink As String
  hyperlink = "https://www.google.com/"
  chromeFileLocation = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
  Shell (chromeFileLocation & "-url " & hyperlink)
    Sheet7.protect "DHH2fanclub"
End Sub

As I hope you can tell, i'm trying to copy the data from cell AF1 in sheet 7 and paste that onto the generic url but don't know the specific commands to paste that value and then successfully open the browser with that completed url. At this moment the shell function fails too.

 

[Legacy 143009]

Sorry for my English. I can't get you 100%. Maybe someone else may help. I can suggest only the code below as I can tell.

hyperlink = "Google" & Sheet7.Range("AF1").Value

 

[JustinLock]

Sorry for my English. I can't get you 100%. Maybe someone else may help. I can suggest only the code below as I can tell.

hyperlink = "Google" & Sheet7.Range("AF1").Value

Perfect, this has fixed it. Thanks!

 

[Legacy 143009]

You should mark my post instead of your post, but no problem Nice to hear it has worked!

 

[Fluff]

@JustinLock in future please mark the post with the solution, rather than your post saying it worked. I have changed it for you this time.

 

[JustinLock]

Apologies all, I was in a rush and thought I did

Thanks again @Flashbond