1) Formula without VBA for know if a sheet is Visible or Hidden and ...

jamiguel77

Board Regular
Joined
Feb 14, 2006
Messages
240
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
Hi all
2 questions:

1) Formula without VBA for know if a sheet is Visible or Hidden ?
2) i have 100 sheets, in first sheet named: General, and the rest: 4-00, 4-01, 4-02 and so so on...
in sheet General, have the names of the sheets (A1 to A100)
4-00
4-01
4-02
....

in B1, how to Reffer to sheet A-00 to cell A1? my goal is point to Shhet of the cell A1, A2... not static similar to: ='4-00'!A8 <---

Any Advice?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,581
.
Here is one answer to your questions ....

This macro goes in a routine module. When you execute the macro, be certain cell A1 on Overview sheet is highlighted. The macro will create a menu list beginning with the cell that is highlighted.

Code:
Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> sh.Name Then
        sh.Range("A1").Value = "=HYPERLINK(""[Create-Links-To-All-Sheets-in-a-Workbook1.xlsm]Overview!A1"", ""Menu"")"
        
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
        ActiveCell.Offset(1, 0).Select
    End If
Next sh
End Sub

I don't believe you will be able to determine by FORMULA only, if a sheet state is visible or hidden. But as always ... I'm probably wrong. :LOL:
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
1) Formula without VBA for know if a sheet is Visible or Hidden ?

I too can't think of a way to do it purely with formula, the nearest that I can think of is to use an UDF.
Please note that the formula won't automatically update when the sheet is hidden, you need to perform an update.

Place the code below in a regular module

Code:
Function ShtVisible(myRng As Range) As Variant
    Application.Volatile
    ShtVisible = CBool(myRng.Parent.Visible = xlSheetVisible)
End Function

and use it in a cell as
=IF(ShtVisible(Sheet2!A1),"Visible","Hidden")
The A1 can be any cell reference on the sheet.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,651
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top