+ Reply to Thread
Results 1 to 26 of 26

Hyperlink Syntax Issue

  1. 07-10-2013, 01:48 PM #1
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Hyperlink Syntax Issue

    All,

    I've been trying for a very long time to get a dynamic hyperlinking formula in vba to work correctly. I was having issues with the simple =Hyperlink() function but found a solution to have the formula work correctly. i have some ranges i need to use within the formula but i can't get the syntax. i've been working on and off on this for 3 days with no success. can anyone take a look:

    Please Login or Register  to view this content.
    what i would like to appear in excel:

    =HYPERLINK("#'OPRATIONS1'!BK9","OPRATIONS1")

    the range 06 would be "OPRATIONS1", the name of the tab.

    Thank you

    edit: i am getting an "Application-defined or object defined error" message.
    Reply With QuoteRegister To Reply
  2. 07-10-2013, 01:54 PM #2
    Lifesigns
    Lifesigns is offline
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Hyperlink Syntax Issue

    Hey welcome to the forum and thanks for using the code blocks.

    Do you have a sample file we could take a look at?
    If the post was helpful please click the black star on the bottom left to add some reputation and mark your thread as SOLVED.

    A day with nothing new achieved or learned, albeit however small, is a day lost forever?

    Constant Never Ending Improvement
    Reply With QuoteRegister To Reply
  3. 07-10-2013, 02:01 PM #3
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    i'm sorry - neither IE or chrome will allow me to open the attachment window.
    Reply With QuoteRegister To Reply
  4. 07-10-2013, 02:04 PM #4
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    You may have popups blocked. Can these be unblocked?
    Rep is appreciated. Click my * if I helped.
    Reply With QuoteRegister To Reply
  5. 07-10-2013, 02:09 PM #5
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    the pop-up is appearing but there is nothing within the window.

    i can email the file?
    Reply With QuoteRegister To Reply
  6. 07-10-2013, 02:12 PM #6
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    Unfortunately, I cannot accept emails. A few other users here may be able to assist though with a link to something like a dropbox...

    [edit] And its rather odd that there is nothing in the popup!
    Reply With QuoteRegister To Reply
  7. 07-10-2013, 02:25 PM #7
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    any way you think you can try without having the file?
    Reply With QuoteRegister To Reply
  8. 07-10-2013, 03:37 PM #8
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    I'll give it a go:
    Please Login or Register  to view this content.
    This somewhat similar to what I am doing. I moved the first '&' outside of the parentheses. Moved around some quotes.
    One thing you could try is:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    This will help in troubleshooting...
    Last edited by ussenterprise; 07-10-2013 at 03:38 PM. Reason: Stuck 'o' key... lol
    Reply With QuoteRegister To Reply
  9. 07-10-2013, 03:42 PM #9
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    Hm. Can't get the MsgBox to run. It might be due to not having all the information. Still working it out...
    Reply With QuoteRegister To Reply
  10. 07-10-2013, 03:45 PM #10
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    I am getting a Method 'Range' of object '_Global' failed (1004 error)...
    Reply With QuoteRegister To Reply
  11. 07-10-2013, 04:01 PM #11
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    Can you paste the whole macro in?
    Reply With QuoteRegister To Reply
  12. 07-10-2013, 04:10 PM #12
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    I ran this:
    Please Login or Register  to view this content.
    I am only returning an 'I' in the box. What is currentRow2? I suspect this is a Dim, but want to be sure...
    Reply With QuoteRegister To Reply
  13. 07-10-2013, 04:25 PM #13
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    If I MsgBox this:
    Please Login or Register  to view this content.
    I get the Range of object failed.

    I removed both ranges and reattempted:
    Please Login or Register  to view this content.
    I get expected: end of statement.

    It looks like it doesn't like the '(' to be inside the quote...

    Still looking...
    Reply With QuoteRegister To Reply
  14. 07-10-2013, 04:32 PM #14
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue 

    Please Login or Register  to view this content.
    works normally.
    Please Login or Register  to view this content.
    works normally (Removed triple quotes here...)
    Please Login or Register  to view this content.
    works normally (removed triple quotes again...)
    Please Login or Register  to view this content.
    works normally. I added the & at the end.

    Try this:
    Please Login or Register  to view this content.
    Last edited by ussenterprise; 07-10-2013 at 04:36 PM. Reason: Let out one tiny character...
    Reply With QuoteRegister To Reply
  15. 07-10-2013, 04:33 PM #15
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    sorry for the delayed response - in some meetings.

    i've went through it so, so slowly and continue to get an end of statement or application defind or object defined error. here is the entire code:

    also, this DID work, meaning it pasted into excel, however, the hyperlink code itself does not function. it needs the #.

    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 07-10-2013, 04:35 PM #16
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    See my post above. I think that'll get it for you.
    Reply With QuoteRegister To Reply
  17. 07-10-2013, 04:37 PM #17
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    i'm starting to think this simply won't work in VB.

    just as a reminder, it has to be in this format:

    =HYPERLINK("#'OPRATIONS1'!BK9","OPRATIONS1")
    Reply With QuoteRegister To Reply
  18. 07-10-2013, 04:37 PM #18
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    still getting the application defined error...
    Reply With QuoteRegister To Reply
  19. 07-10-2013, 04:40 PM #19
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    Let me take another look.
    Reply With QuoteRegister To Reply
  20. 07-10-2013, 04:46 PM #20
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    maybe let's try to simplify it by taking the ranges out and just making the cells static. once the code will go into excel correctly then maybe move the dynamic ranges in?
    Reply With QuoteRegister To Reply
  21. 07-10-2013, 04:49 PM #21
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    Hm... strangeness on this one.
    I'll have to continue tomorrow.
    Reply With QuoteRegister To Reply
  22. 07-10-2013, 04:49 PM #22
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue

    Quote Originally Posted by sgrande01 View Post
    maybe let's try to simplify it by taking the ranges out and just making the cells static. once the code will go into excel correctly then maybe move the dynamic ranges in?
    Hm. One sec...
    Reply With QuoteRegister To Reply
  23. 07-10-2013, 04:54 PM #23
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    something that is also strange - this works fine in a msgbox but not when assigning it to a cell. makes me think something is going on in the back end or that you can't bring this formula in from vb to excel?

    Please Login or Register  to view this content.
    the above code is set in a new workbook with currentrow the only variable
    Reply With QuoteRegister To Reply
  24. 07-10-2013, 04:58 PM #24
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue 

    Please Login or Register  to view this content.
    This appears to work as intented. At least in the MsgBox.
    And... I see what you mean. I have an idea, but it may take me a while to research it...
    Reply With QuoteRegister To Reply
  25. 07-10-2013, 05:05 PM #25
    ussenterprise
    ussenterprise is offline
    Forum Contributor
    Join Date
    07-19-2012
    Location
    Redmond, WA
    MS-Off Ver
    O365
    Posts
    133

    Re: Hyperlink Syntax Issue 

    Please Login or Register  to view this content.
    Here's my first attempt. Getting an error. I'll continue tomorrow.
    Reply With QuoteRegister To Reply
  26. 07-10-2013, 05:27 PM #26
    sgrande01
    sgrande01 is offline
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Hyperlink Syntax Issue

    i got it:

    Please Login or Register  to view this content.
    thanks for the help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1