+ Reply to Thread
Results 1 to 7 of 7

Only unhide sheet when hyperlink is clicked that directs to the sheet

  1. #1
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Only unhide sheet when hyperlink is clicked that directs to the sheet

    I have a Main TAB that has links to sheets Apple, Orange & Pear. I always want sheets Apple, Orange & Pear hidden UNLESS I click the link on the Main TAB that directs me to one of those sheets. In that case, the sheet would unhide until it is no longer active, then it would go back to hidden.

    My final workbook will have a Main TAB and 50 other tabs (Apple, Orange, Pear, Grape, Banana, Kiwi.... etc.). All the hyperlinks will already be on the tabs. Each hyperlink has a random defined name (eg. "_30542226" "_91303912"... etc.). I am looking for a macro that can identify when the link from the Main TAB is referencing to a defined name on another sheet, then unhide it. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Only unhide sheet when hyperlink is clicked that directs to the sheet

    Add code in Workbook_SheetFollowHyperlink method.
    Last edited by hafizimran; 01-11-2014 at 02:59 PM.

  3. #3
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Only unhide sheet when hyperlink is clicked that directs to the sheet

    Ok, this gets the destination defined name:

    HTML Code: 
    How can I get the destination sheet?

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,227

    Lightbulb Re: Only unhide sheet when hyperlink is clicked that directs to the sheet


    Hi,

    'cause the SheetFollowHyperlink event starts after the link process,
    better should to remove links and use buttons !

    Anyway, with a few tweaks … Code to insert in the ThisWorkbook module :

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Only unhide sheet when hyperlink is clicked that directs to the sheet

    Works great, thanks!
    Attached Files Attached Files

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Only unhide sheet when hyperlink is clicked that directs to the sheet

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,227

    Lightbulb A better way

    Please Login or Register  to view this content.
    Any chance to see my reputation growing ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create new sheet from template, auto fill from index sheet and hyperlink
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2013, 04:57 AM
  2. [SOLVED] Hide / Unhide Row on a Sheet based on the Value of a Cell on Another Sheet
    By neslotna in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2013, 12:23 PM
  3. unhide sheet, copy data, hide sheet - screenupdate = false
    By titus.hanke in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2008, 02:46 PM
  4. [SOLVED] HELP!! Unhide Sheet with Macro and focus on other sheet
    By saraqpost@yahoo.com in forum Excel General
    Replies: 2
    Last Post: 05-23-2006, 02:20 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1