Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Vba Hyperlink To Local Worksheet

  1. #1
    Join Date
    7th March 2007
    Posts
    50

    Vba Hyperlink To Local Worksheet

    I would like to, using VBA, assign a hyperlink to an excel cell that links to another worksheet within the same workbook. I've tried the forums, and combined with a previous post and MS Excel Help File, I've come up with the following attempt:
    VB:
    With Worksheets("Test Destination") 
        .Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:=strHyper2, _ 
        TextToDisplay:=(#1/1/2007# + counter) 
         
    End With 
    
    
    strHyper2 is previously defined:

    VB:
    strHyper2 = "[WeatherTester.xls]1!A1" 
    
    
    My spreadsheet file is "WeatherTester.xls", my worksheet is "1", and "A1" is the desired link location for the cursor.

    Counter is an integer.

    I get the error "Invalid procedure call or argument"

    I also tried to enter a hyperlink directly through the formula property, but Excel didn't like the single quotations I had to use within the HYPERLINK function. The hyperlink formula works in the following form:

    VB:
    =HYPERLINK("[WeatherTester.xls]1!A1","1 Jan") 
    
    
    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,052

    Re: Vba Hyperlink To Local Worksheet

    Try amending this, note address is blank & I used subaddress, also replaced # with "

    VB:
    Const strHyper2 As String = "Sheet2!A1" 
    Dim counter As Integer 
    counter = 4 
    With Worksheets(1) 
        .Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:="", _ 
        SubAddress:=strHyper2, TextToDisplay:=("1/1/2007" & counter) 
         
    End With 
    
    
    Last edited by royUK; March 7th, 2007 at 15:56.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    7th March 2007
    Posts
    50

    Re: Vba Hyperlink To Local Worksheet

    Thanks for the help. The subaddress thing definitely worked, although I can't quite get the text display deal to work. Basically each iteration through the loop represents a day after 1 Jan 2007, so I'm trying to get the display date to increment with the hyperlink. The date as stated with the string & counter creates hyperlinks that look like: 1/1/20071. It seems like it should be easy, but its not working the way I think it should.

    But I'll keep working on that. Thanks for your help!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2007
    Posts
    50

    Re: Vba Hyperlink To Local Worksheet

    If you care, I fixed the "TextToDisplay" problem... I just declared a string variable and set it equal to the desired date. I used that variable as the argument and excel liked it better. Thanks again.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Named Range Becomes Local When Worksheet Copied
    By pandemar in forum EXCEL HELP
    Replies: 2
    Last Post: September 30th, 2006, 16:45
  2. HyperLink to Pictures saved on a Local HD
    By Michael Avidan in forum EXCEL HELP
    Replies: 3
    Last Post: July 16th, 2006, 22:50
  3. Hyperlink set up within a worksheet
    By mlf30066 in forum EXCEL HELP
    Replies: 3
    Last Post: February 2nd, 2006, 02:31
  4. hyperlink function to link to open a local file...
    By mjschukas in forum EXCEL HELP
    Replies: 1
    Last Post: September 21st, 2004, 18:36
  5. How to do the hyperlink in the same worksheet?
    By yyama8 in forum EXCEL HELP
    Replies: 2
    Last Post: May 6th, 2004, 01:39

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno