1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How To Hyperlink To Cells In Hidden Worksheet

Discussion in 'Excel Dashboards' started by Lim Aina, Sep 10, 2014.

  1. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
    Hi ,

    If you can use Debug , in the Immediate window , can you type in :

    ?sheetname

    and see what is displayed ?

    The sheet name is the problem , and unless we know what is being used instead of DIN or ISO , there is no way we can proceed.

    Narayan
  2. Yatri

    Yatri New Member

    Messages:
    3
    Guys.... I am using a very similar sheet. I am able to navigate away from the other worksheets. However, when back in home it doesn't seem to accept the links in the home worksheets.

    Also those sheets identified in the code shared by Narayana aren't showing up under the 'unhide' list (on right clicking the home tab).
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
    Hi ,

    Can you upload your workbook ?

    Narayan
  4. Yatri

    Yatri New Member

    Messages:
    3
    Hey thanks for your reply Narayan. It was an old thread, I was not sure if it's still active.
    Please see the attached template. The final file is quite the same.


    This is the code (copying from above) I used in the original file.

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If ((Sh.CodeName = "Sheet8") Or (Sh.CodeName = "Sheet13") Or (Sh.CodeName = "Sheet14") Or (Sh.CodeName = "Sheet16") Or (Sh.CodeName = "Sheet17") Or (Sh.CodeName = "Sheet12") Or (Sh.CodeName = "Sheet4")) Then
    Sh.Visible = xlSheetVeryHidden
    End If
    End Sub

    (*** CodeNames in attached file have been adjusted)

    Sub TextBox_Click()
    shtname = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
    With Worksheets("Home")
    .Visible = xlSheetVisible
    .Activate
    .Range("A1").Select
    End With
    End Sub

    Also when I do this, the worksheets go 'hidden' but I can't find them to 'unhide' even after I removed the code completely.


    Sorry ... I have just started introducing myself to VBA and will be doing a lot of work towards it in coming weeks.

    Mighty appreciate your help.

    Cheerio

    Attached Files:

  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
    Hi ,

    See if this is what you wanted.

    Narayan

    Attached Files:

    Thomas Kuriakose and Yatri like this.
  6. Yatri

    Yatri New Member

    Messages:
    3
    thank you my friend. Helps majorly.

    I also understood the difference between xlhidden and xlveryhidden :), getting there.

    This works.. I'll test it on the original file tomorrow morning.

    However, for this to work I noticed tat the names in the text box needs to match the the worksheet name. Is it even referring t the hyperlink I created or simply searching the sheet with same name? Reading the code, I suspect the latter.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
    Hi ,

    I deleted all the hyperlinks , since if the hyperlinks are in place , action will be taken by Excel on the hyperlinks , and the macro never gets executed.

    The macro is needed to unhide the hidden worksheet.

    The names have to match only because use is being made of the Application.Caller method , which tells the code which shape was clicked ; if use were not to be made of this , we will have to use a lookup table to match the shape name with the worksheet tab.

    Narayan
    cyliyu and Yatri like this.
  8. cyliyu

    cyliyu Member

    Messages:
    97
    Is it possible control the access of certain button?
    For e.g. all user only allow to access FCR and NPS, the rest of the buttons only admin can access?
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,815
    Hi ,

    Please start a new thread for this question.

    You can ensure that some controls are enabled / disabled through code ; this same code can then prompt the user to enter a password.

    Narayan

Share This Page