creating hyperlink through vba

creating hyperlink through vba

• vannielou

  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

   

  
  
  

• OndraKKK

  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

  
  

• jzfredricks

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

• harlequinben

  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

  
  

• Markus Sch.

  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

  
  
  

• Pocketmnky

  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

  
  
  

• Danny Tuppeny

  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...
  
  

• Glenn Wellington

  Try using the activesheet object like this:

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

  
  
  

• Todd Biggs - Windows Live

  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 :)
  
  

• mNero

  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
  
  

• MillBear

  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
  
  
• creating hyperlink through vba