Hyperlink to hidden worksheet & hide worksheet automatically after done on the hidden worksheet

Hyperlink to hidden worksheet & hide worksheet automatically after done on the hidden worksheet

Post by Lam » Sun, 28 Feb 2010 02:49:13


yperlink To Hidden Worksheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Worksheets(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)).Visible = xlSheetVisible
Application.EnableEvents = False
Target.Follow
Application.EnableEvents = True
End Sub



Hide Worksheet Automatically

Private Sub Worksheet_Activate()
Me.Visible = xlSheetVisible
End Sub

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub



Gladwin Ram wrote:

Dealing with Hidden Sheets
03-Nov-09

Hi Chip,

Thank you for the code to open hidden sheets through hyperlinks. It works well. Is there a way to hide the sheet back once I'm done working on that opened hidden sheet?

Previous Posts In This Thread:

On Saturday, September 12, 2009 9:36 AM
stew wrote:

Hidden Worksheets
Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

On Saturday, September 12, 2009 12:54 PM
Chip Pearson wrote:

You need to use some code to make the target worksheet visible and thecall the
You need to use some code to make the target worksheet visible and the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible <> xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sunday, September 13, 2009 2:43 AM
stew wrote:

Hi ChipRight clip on the tabthen I assume View codeThen paste in MacroIs there
Hi Chip

Right clip on the tab

then I assume View code

Then paste in Macro

Is there anything else I need to do

I did that on my Hyperlink sheet and then hid two of my sheets and tried to
accesss them with the hyperlinks. No go. Unhide them, all ok

I am not very familiar with macros but you have the Gist of what I want to
achieve.

What am I missing

Best

Stew

"Chip Pearson" wrote:

On Sunday, September 13, 2009 7:54 AM
Chip Pearson wrote:

Your procedures for using code seem to be fine.
Your procedures for using code seem to be fine. I tested the code in
XL2003 and XL2007. What version are you using? Also, the code works
only with Hyperlinks created via the Insert menu. It will not work
with hyperlinks that are created with the HYPERLINK worksheet function
-- clicking on a link created with the HYPERLINK worksheet function
does not raise an event so there is no way to unhide the sheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Soft
 
 
 

1. How do I detect hidden worksheets or hidden data on a worksheet?

2. FYI Excel 2007: Saving a xlsm file with a hidden worksheet activated makes worksheet visible

I have an Excel application that copies data from one file into hidden
worksheets in 2 other files. When I updated to Excel 2007, one of the
hidden worksheets kept appearing after I ran the data copy procedure
and reopened the target file.

The only difference between this reappearing worksheet and the other 9
hidden sheets was the fact that the code left the reappearing
worksheet active. (I set Excel's screenupdating=false while opening
the target files, moving all the data, and then closing/saving the
target files.) In 2003, Excel would not change the worksheet's visible
property, even though the worksheet had been activated while
screenupdating was set to "false".

I added one line of code, which set one of the visible worksheets
active before saving the file. Once again the hidden sheets remain
hidden, all of them.

Hope this is informative.
THANKS!
David G.

3. Hiding worksheets and rows in a worksheet permanently

4. How do I hide a worksheet in Excel and use a password to un-hide .

5. How to print worksheet and automatically hide zero value rows?

6. How to hide toolbar automatically when I switch to other worksheet

7. Worksheet that automatically hides removes certain column pasted i

8. How to hide toolbar automatically when I switch to other worksheet

9. How do I automatically hide columns in a worksheet based on a cell value?

10. Copy worksheets to new worksheet and add a worksheet name column t

11. Copy worksheets to new worksheet and add a worksheet name column to new sheet.

12. Copy from 1 worksheet to multipule worksheets based upon worksheet name

13. protected worksheet and hidden columns crashes excel

14. Excel to Access (hidden worksheet | linked cells)

15. hidden columns disappear after I unprotect worksheet