No announcement yet.

Vba Hyperlink To Local Worksheet

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
        With Worksheets("Test Destination")
           .Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:=strHyper2, _
                TextToDisplay:=(#1/1/2007# + counter)
        End With
    strHyper2 is previously defined:

    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:

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

  • #2
    Re: Vba Hyperlink To Local Worksheet

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

    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, 16:56.
    Hope that Helps


    New users should read the Forum Rules before posting

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


      • #4

        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.