creating hyperlink through vba

Question

hi,

i'm facing a bit of a situation that's been driving me nuts for about 12 hours now. in a nutshell, i have a lot of data in access, where i use vba to crunch numbers, and from the same project, i open an existing excel file (thereby now using both excel and access functionality from the same vba code), where i need to paste and format the crunched data (reports) to look pretty, and be user friendly.

in the user friendly part, i dynamically need to create hyperlinks from one cell to another cell (in the same workbook, and some even within the same sheet). it seemed straightforward enough, but i have tried EVERY possible approach...using the (worksheet).hyperlinks.add function, as well as trying (worksheet).(cell).formula = "=hyperlink("..","..."). nothing seems to work at all. i do not get an error, but essentially no outcome of those lines is seen in my excel file. also, all statements following this statement, within that code block, are ignored.

any help would be greatly appreciated!

thank you,
kriti

Answer 1

Try using the activesheet object like this:

Range("D7").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "Http://www.Google.com/", TextToDisplay:="Google"

Answer 2

Hi DMan,

thanks for replying so quickly. i've tried a bunch of stuff like that. interestingly, if i provide an "address" argument (to a website), it works. however i need to create a link to another cell on the same sheet. so the subaddress argument needs to be populated, not the address. i've tried using things like
     dim xlSht as Excel.sheet
     set xlSht = (xlWbk).Sheets("Sheet1")
     xlSht.hyperlinks.add .....
which i think is basically what you suggest.

i've also tried setting the value of a cell to the hyperlink formula...like
     xlSht.cells(4,5).value = "=hyperlink(....)"
and so on.

the situation is in fact further complicated because both the anchor and destination cells are dynamic and change with the code...so i am using variables to specify the row/column. that shouldn't affect it though, as long as it's returning a range object.

the interesting factor still remains that if i specify a url as the address, it works (even through vba), but getting it to link to another cell in the same sheet just does not work at all.

any ideas?

thank you once again,
kriti

Answer 3

If you are trying to hyperlink to a range in the workbook...use Application.Goto

Application.Goto Reference:="R1C26"

 

You can also use the sheets onchange event to do something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Text = "TheText" Then
      Application.Goto Reference:="R1C26"
   End If
End Sub

 

Answer 4

hi again,

i dont think the onchange event would be of much use, because i dont want the user to be editing anything in that excel file anyway. could you elaborate on the application.goto function? would i have to link it to the onclick event for the "anchor" cell?

thanks :)

Answer 5

kriti,

The selection change event fires anytime focus changes from one cell to the next...If you are trying to navigate within the workbook use application.goto and yes you can use it in the onclick event

Answer 6

hey,
could you elaborate just a little bit how i would use the application.goto with the onclick event? i need the cell i pick to behave just like a hyperlink, regardless of the coding behind it.
thank you...

Answer 7

kirti,

If you use the code i gave you it will check to see what the text is in the cell that the user entered if it matches the application will goto the reference which is designated as  row and column number.  You can place this in the button click event or you can have "google" on the page 10 times and the user will always be taken to the refernced GOTO....

Private Sub CommandButton1_Click()
Application.Goto Reference = "R3C3"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Text = "Google" Then
 Application.Goto Reference = "R1C1"
End If
If Target.Text = "MSN" Then
Application.Goto Reference = "R2C2"
End If
End Sub

From here i would suggest getting a good book on VBA and also Look into VSTO...but for now you can download the VBA references from here:

http://msdn.microsoft.com/office/download/vba/default.aspx

Answer 8

dman,

i seem to realize that the problem is somewhere else...the link is being created, but no matter what different form i use (other than a web address), i get the same error saying "Cannot open specified file." i even tried WITHOUT vba, just going into a blank excel file, and typing into a cell:

=hyperlink("[book1]sheet1!A5","click here")

which is the exact format specified in the excel 2000 help file under the topic of "hyperlink worksheet function"...it still doesn't work.

the only form of hyperlink that DOES work, is if i right click on a cell and click on hyperlink, and use that "wizard".

what problem could this be?

thanks,
kriti

Answer 9

i should clarify, the error comes when i actually click on the link, not during its creation.

Answer 10

hi,

i would just like to post that i have found a solution to the problem. it seems annoyingly simple that i did not stumble upon it before, but it is essentially the same as my code in the previous post, except the workbook name has a .xls at the end. i.e., by putting the following as a formula in any cell of any worksheet in the workbook named book1.xls, it will take me to the cell D10 on sheet1 within that workbook:

=hyperlink("[book1].xls]'sheet1'!D10","click here")

i just adapted it to vba by setting the value of what would normally be my anchor cell, to the above statement.

for the benefit of anyone else that might read this post, the single quotes around sheet1 are only required if the name of the worksheet in question has spaces in it.

i do have another dilemma now. how can i move a particular cell to the top-left corner of the sheet? i.e., if, right now, i have A1 hyperlinked to B65, when i click on A1, it is current taking me to B65, with row 65 as the last visible row on the screen. however, i want it to be the first visible row on the screen. any suggestions would be greatly appreciated.

thank you...

sincerely,
kriti

Answer 11

I'm trying to use the above method and get an error when I try to set the value to a range.

The code is like this:

Dim txt As String

Sheets("Sheet_Name").Select

Range("A26").Select

txt = "=HYPERLINK(\""[File Name.xls]'Sheet Name'!A1\"",\""TO TOP\"")"

Range("a26").Value2 = txt

 

when the last line runs, I get this error:

Run time error 1004

Application defined or object-defined error

if I go into the debugger and mouse over the txt part of the line, I get a popup window that shows me that txt has the value I want it to.

If I replace txt with something like Range("a4").value2 and a4 has text in it, that works.

What is the difference?

 

 

Answer 12

This is a really old post, but mostly for the benefit of others:

Especially with new versions of Excel the "Record Macro" feature in Developer is really handy way to figure out what the vba script looks like for something you're trying to do.  For example, if you make a hyperlink to somewhere else in the document, you get this right out of excel by looking at the macro created during recording:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Sheet1!A129", TextToDisplay:="111-111111"

To make your own code, simply modify this. In my application I have ID numbers in col A and copied the name of the sheet into column D. I used the following script to go find  the parent of these IDs (in 111-111111 format) and hyperlink to them elsewhere in my workbook:

Private Sub makeHyperlinks()

Dim hyp As Hyperlink
Dim wsSheet, checkSheet As Worksheet
Dim subAddString As String
Dim rowVal
Set wsSheet = Sheets("Sheet2")
Dim startRow
startRow = 3
'row1 = startRow
'row2 = 0

SheetLastRow = wsSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = 3 To SheetLastRow
    If WorksheetFunction.CountIf(wsSheet.Cells(i, 1), "???-??????") Then
        rowVal = i
        Set checkSheet = Sheets(wsSheet.Cells(rowVal, 4).Value)
        Set rFoundCell = checkSheet.Columns(1).Find(What:=wsSheet.Cells(rowVal, 1).Value, After:=checkSheet.Cells(1, 1), _
                                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                                SearchDirection:=xlNext, MatchCase:=False)

        subAddString = "" & checkSheet.Name & "!" & rFoundCell.Address
            wsSheet.Hyperlinks.Add Anchor:=wsSheet.Cells(rowVal, 1), Address:="", SubAddress:= _
            subAddString, TextToDisplay:=wsSheet.Cells(rowVal, 1).Value
            
         End If
Next i

MsgBox "done at row: " & i
End Sub

 

Enjoy!