+ Reply to Thread
Results 1 to 14 of 14

Clicking Link to Hidden Sheet - Unable to get visible property

  1. #1
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Clicking Link to Hidden Sheet - Unable to get visible property

    Hello,

    I am very new to VBA and following a few tutorials online to try and click a link on a main sheet to access data on a hidden sheet and then have it hidden again. When I use the following macro, I get a bug error message. I have attached a screen show of the debugger and my spreadsheet. There is no data on it yet, just the sheets and links as I want to make sure this will work before doing it.

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    linkto = Target.SubAddress
    wherebang = InStr(1, linkto, "1")
    If wherebang > 0 Then
    mysheet = Left(linkto, wherebang - 1)
    Worksheets(mysheet).Visible = True
    Worksheets(mysheet).Select
    myaddr = Mid(linkto, wherebang + 1)
    Worksheets(mysheet).Range(myaddr).Select
    End If

    End Sub

    Appreciate the help!
    Ashley
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,162

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    Try...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    I tried that and I get the same error message.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,141

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    I think you'll find wherebang should be looking for an exclamation mark, not a 1:

    Please Login or Register  to view this content.
    Rory

  5. #5
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    I tried that and it says Error 9: subscript out of range. The debugger still highlights the same line of
    Worksheets(mysheet).visible=true

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,141

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    What's the value of mysheet at the time? In your sample workbook, I get a whole file path in the hyperlink, so your code won't work.

  7. #7
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    I'm not sure. I followed the code given from a Mr. Excel video on YouTube

    I just have each field linked to the correct worksheet (which are hidden)

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,141

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    When the error occurs, press Debug, then hover the cursor over the mysheet variable in the highlighted line. You will get a little tooltip telling you the current value.

  9. #9
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    It's the name of the worksheet I tried to click on that is hidden. It says mysheet= "WE 1-16-21"

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,162

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    This should get you there...
    Please Login or Register  to view this content.
    ...and when you leave the worksheet...
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    That works great! If I use the deactivate it currently hides my main sheet when another link is clicked. Is there a way to apply that to every sheet except the main one? I'd like that one to always be visible.

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,162

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    Put the deactivate code only in the worksheets you wish to keep hidden.

  13. #13
    Registered User
    Join Date
    01-27-2021
    Location
    Michigan
    MS-Off Ver
    365
    Posts
    8

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    Thank you so much!!! I appreciate you.

  14. #14
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,162

    Re: Clicking Link to Hidden Sheet - Unable to get visible property

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Unable to set visible property of the pivotitem class
    By countryfan_nt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2017, 04:26 PM
  2. Error on Sheet Name Change: "Unable to set the Hidden Property of the Range Class"
    By mandora in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2013, 06:10 PM
  3. unable to set the visible property of the pivotitem class
    By goldfrapp01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2013, 12:27 PM
  4. Pivot Table - unable to set visible property?
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2010, 06:11 AM
  5. Unable to set the visible property of the worksheet class
    By colinnwn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-09-2009, 03:48 PM
  6. Unable to set Hidden Property
    By LAF in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2005, 07:05 AM
  7. unable to set hidden property
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2005, 08:06 PM

Tags for this Thread

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