very hidden sheets are very hidden

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 2, 2007

• #2

Press Alt+F11 to go to the Visual Basic Editor. Select the sheet(s) in the Project window and change the Visible property in the Properties window.

 

Upvote 0

Darren Bartrup

Well-known Member

Joined

Mar 13, 2006

Messages

1,297

Office Version

1. 365

Platform

1. Windows

• Jan 2, 2007

• #3

Press Alt+F11 to bring up the VBE window, make sure the Project Explorer is visible in the VBE window (Ctrl+R). This will give a list of all worksheets in the workbook.

Open the Immediate window (Ctrl+G) and type:

Code:

Sheet1.Visible = xlSheetVisible

NB: Change Sheet1 to the sheet you're interested in - Sheet1 is the name not in brackets in the Project Explorer.

Edit: I always seem to do it the hard way Andrew

 

Upvote 0

M

muppet77

Board Regular

Joined

Jan 24, 2004

Messages

223

• Jan 2, 2007

• #4

thanks for the rapid response!

i'm not sure if i have it right, as the explorer does not show the sheets i am after?!?

perhaps they are not sheets, but just references?

 

Upvote 0

M

muppet77

Board Regular

Joined

Jan 24, 2004

Messages

223

• Jan 2, 2007

• #5

i have located it as being a named range in a hidden sheet?
i have the password, but how can i 'reveal' it?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 2, 2007

• #6

To unhide a hidden Name:

ActiveWorkbook.Names("TheName").Visible = True

Change "TheName" to suit.

 

Upvote 0

M

muppet77

Board Regular

Joined

Jan 24, 2004

Messages

223

• Jan 2, 2007

• #7

do i just paste this into the vba sheet and then run?
that doesn't seem to work?

 

Upvote 0

the magician

Active Member

Joined

Nov 9, 2006

Messages

496

• Jan 2, 2007

• #8

As Delmar wrote:

Press Alt+F11 to bring up the VBE window, make sure the Project Explorer is visible in the VBE window (Ctrl+R). This will give a list of all worksheets in the workbook.

Open the Immediate window (Ctrl+G) and type:

Click to expand...

Now you can type in many various VBA codings (one at a time) and press Enter and they will execute immediately.

Type in (or copy and paste from above):

ActiveWorkbook.Names("TheName").Visible = True

Click to expand...

and press Enter. Change "TheName" for the name you want to unhide.

This is sort of a "shortcut" to writing a whole VBA macro, if you only need to do one thing.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 2, 2007

• #9

do i just paste this into the vba sheet and then run?
that doesn't seem to work?

Click to expand...

Try putting this in a General module and running it:

Code:

Sub Test()
Dim Nm As Name
For Each Nm in ActiveWorkbook.Names
   Nm.Visible = True
Next Nm
End Sub

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Jan 2, 2007

• #10

Ummm, are we barkin' up the wrong tree, lads???

Per the OP:

i have tried "format, sheet, unhide" but that is greyed out.

Click to expand...

The only time (in my experience) that this menu option is disabled is when

• There are no hidden sheets (or - as may be the case here - the only hidden sheets are very hidden) or
• The workbook's structure is protected.

If it's the latter then Tools | Protection | Unprotect Workbook... and enter the password (if required).

HTH

 

Upvote 0