• 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...

  • 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

NARAYANK991

Excel Ninja
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
 

Yatri

New Member
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).
 

NARAYANK991

Excel Ninja
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).
Hi ,

Can you upload your workbook ?

Narayan
 

Yatri

New Member
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
 

Attachments

Yatri

New Member
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.
 

NARAYANK991

Excel Ninja
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

Member
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?
 

NARAYANK991

Excel Ninja
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?
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
 
Hi ,

See the attached file.

Narayan
Hi Narayan

I think I caught the Oliver Twist bug...is it possible to create hyperlink to the other hidden cells? I notice I have a data for my drop down list in one of the hidden cells and this is no longer active on the Staff list worksheet. I want to create additional 2 hyperlinks. Pardon my for the bother.
 
Top