Solved: Open URL that includes string picked up from cell

[Rejje]

Hi!

How can I make this happen?

1. One alters cell Range("CUSTOMER_NAME")
2. Message box: "Do yow wish to open web browser to see the customers address?" vb Yes/No
3. If answer "Yes" browser opens "http//www.etc_1" & Range("CUSTOMER_NAME").Value & "etc_2.com"

Thanks in advance!

[mancubus]

below code automatically requires confirmation to open the specified page whenever the Range("CUSTOMER_NAME")) is changed.
put it in the code module of the worksheet which houses Range("CUSTOMER_NAME"))

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("CUSTOMER_NAME")) Is Nothing Then Exit Sub
Dim resp, sUrl1$, sUrl2$, sUrl$
sUrl1 = "http//www.etc_1"
sUrl2 = "etc_2.com"
sUrl = sUrl1 & Range("CUSTOMER_NAME") & sUrl2

resp = MsgBox("Do yow wish to open web browser to see the customers address?", vbYesNo)
If resp = vbYes Then
ThisWorkbook.FollowHyperlink sUrl
Else
End If

End Sub
[/vba]


if you wish to assign a button then, in a standard module;
[vba]
Sub open_url()

Dim resp, sUrl1$, sUrl2$, sUrl$
sUrl1 = "http//www.etc_1"
sUrl2 = "etc_2.com"
sUrl = sUrl1 & Range("CUSTOMER_NAME") & sUrl2

resp = MsgBox("Do yow wish to open web browser to see the customers address?", vbYesNo)
If resp = vbYes Then
If Range("CUSTOMER_NAME") = "" Then
MsgBox "No customer specified in cell"
Exit Sub
Else
ThisWorkbook.FollowHyperlink sUrl
End If
Else
End If
End Sub
[/vba]

[Rejje]

Works - great solutions!

Actually I have incorprated both above suggestions into two different assignment in my workbook (your button suggestion in fact spared me initializing another thread in here).

Thanks a lot Mancubus!

Rejje