• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 733
  • Last Modified:

excel vba to embed a hyperlink within a cell based on the cell's value

I would like to embed a hyperlink into a cell where the ending of the cell is part of the link.
ie.

Cell value is [ticket number] the url is "http://server/page.aspx?iid=[ticket number]

i then have a button that copies all cells and pastes them as 'value' and e-mails it to my director.

any help you could provide would be appreciated.
0
CMWinters
Asked:
CMWinters
  • 3
  • 2
6 Solutions
 
dougaugCommented:
Insert a new module in your workbook and add the following function:

Function CreateHyperlink(URL As String, aCell As Range) As String
  CreateHiperlink = URL + aCell.Text
End Function

In your worksheet, add the following formula in a cell:
=CreateHyperlink("http://server/page.aspx?iid=";A1)
0
 
zorvek (Kevin Jones)ConsultantCommented:
You don't need VBA. Use the HYPERLINK function:

=HYPERLINK("http://server/page.aspx?iid="&A1)

Kevin
0
 
SiddharthRoutCommented:
Is this what you are trying?

Sample File Attached

Code Used

Private Sub CommandButton1_Click()
    Dim aCell As Range
    Dim strURL As String, MyArray() As String
    
    strURL = "http://server/page.aspx?iid=4"
    
    MyArray = Split(strURL, "=")
    
    strSearch = Trim(MyArray(1))
    MsgBox strSearch
    Set aCell = Sheets("Sheet1").Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        Sheets("Sheet1").Hyperlinks.Add Anchor:=aCell, Address:=strURL
    Else
        MsgBox "Not Found"
    End If
End Sub

Open in new window


Sid
hyperlink-example.xls
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
Sorry. Remove Line 10 from the above code.

Sid
0
 
zorvek (Kevin Jones)ConsultantCommented:
A caveat: The ticket number has to be in a separate cell.

I would implement it like this:

Put the ticket number in column A. In column B use this formula:

=HYPERLINK("http://server/page.aspx?iid="&A2,A2)

This will create the hyperlink in column B and display the ticket number.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Then hide column A so that only column B is visible as ticket numbers that link to a web page.

Kevin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now