Solved

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

Posted on 2011-02-25
6
722 Views
Last Modified: 2012-05-11
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
Comment
Question by:CMWinters
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Assisted Solution

by:dougaug
dougaug earned 83 total points
ID: 34983884
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 251 total points
ID: 34983926
You don't need VBA. Use the HYPERLINK function:

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

Kevin
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 166 total points
ID: 34983942
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 166 total points
ID: 34983950
Sorry. Remove Line 10 from the above code.

Sid
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 251 total points
ID: 34983955
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 251 total points
ID: 34983970
Then hide column A so that only column B is visible as ticket numbers that link to a web page.

Kevin
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question