Making Private sheets visible to macros

05Li139

New Member
Joined
Nov 10, 2016
Messages
8
Using the following two subs to make all worksheets apart from the index page hidden, and then to make hidden sheets visible to hyperlinks so that I can have an index page which links to hidden tabs, as the database I'm managing is over 100 worksheets.

Private Sub Worksheet_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, "Infopages_001_Index", vbTextCompare) = 0 Then
ws.Visible = False
End If
Next ws
End Sub


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Worksheets(Target.Range.Value)
.Visible = True
.Activate
.Range("A1").Select
End With
End Sub


Is it possible to modify this line "Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)", to work so that when I run a macro it will look for hidden as well as visible sheets?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I think you misunderstand the meaning of Private in this context

"Private" controls the visibility of macros depending on where you are within the VBA Project. Thus these macros are visible only from within the Worksheet code module - if you are working on another worksheet then you can't see this code. However, this code can see out into the broader workbook if you refer to things correctly. It just can't see private code within other code modules. Hopefully that makes sense. In a general code module there's nothing stopping you from adding Private to a piece of code, but usually you don't, so any other code can see code in that module

The visibility of an Excel worksheet is a completely separate issue. Any piece of code can be told to see that worksheet, and it's visibility to the User does not change what VBA can see

The issue here is more that the code you have posted is stored within say Sheet1 VBA code module, and hence only relates to Sheet1. For it to affect Sheet2, it would also have to be duplicated in Sheet2

But there's other Events that you can probably use, found within the ThisWorkbook code module. In that module you will find Workbook level events such as
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
and
Code:
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
I suspect these will suit your purposes better
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top