MrExcel Publishing
Your One Stop for Excel Tips & Solutions

determining the Visible value of a worksheet


Posted by Andrew on April 05, 2001 12:49 AM

I am trying to find the Visible value of a worksheet, and if it is False, to make it visible. Otherwise I go on to preform another task. Can anyone help?

I thought this would work, but am having no luck.

If Worksheets(x).Visible = False Then

Sheets(x).Visible = True

Else


Posted by Dave Hawley on April 05, 2001 1:38 AM

Sheets(x).Visible = True Else


Hi Andrew


That should work, or rather it does work. Try this

Sub ThisWorks()

If Sheets("Sheet1").Visible = False Then
Sheets("Sheet1").Visible = True
End If

End Sub


But why bother checking, you can just use:
Sheets("Sheet1").Visible = True

Dave


OzGrid Business Applications

Posted by Andrew on April 05, 2001 2:03 AM

Sheets(x).Visible = True Else

Thanks Dave

I'm not sure why it's not working, but it errors saying 'Unable to get the Visible property of the Sheets class'

I have a Workbook with several sheets in it. On a main screen users selcet the sheets they want to use and the rest are hidden. Sometimes they require a hidden sheet, or a copy of a visible sheet. That is why I am checking if the sheet is hidden, if not I run a routine to make a copy of the sheet they are after.


Andrew

Posted by Dave Hawley on April 05, 2001 3:01 AM


Hi Andrew

I would guess that you have protection of some sort set. Try this code it should cover all bases and set all protection back to as was.

Notice I use the sheet CodeName this can be found in the Properties window of the Worksheet.


Sub ThisWorks()
Dim VeryHidden As Boolean
Dim WindowProtect As Boolean
Dim StructureProtect As Boolean

'set Boolean variable to false
VeryHidden = False

'Pass protection True or False to Boolean variables
WindowProtect = ActiveWorkbook.ProtectWindows
StructureProtect = ActiveWorkbook.ProtectStructure

'Unprotect Workbook
ActiveWorkbook.Unprotect Password:="secret"

'Check if very hidden, if so set Boolean variable to false
If Sheet1.Visible = xlSheetVeryHidden Then VeryHidden = True
Sheet1.Visible = xlSheetVisible

'Unprotect the sheet and copy after sheet2
Sheet1.Unprotect Password:="secret"
Sheet1.Copy after:=Sheet2 'CodeName

'If sheet1 was veryhidden put back to veryhidden
If VeryHidden = True Then
Sheet1.Visible = xlSheetVeryHidden
Else
Sheet1.Visible = xlSheetHidden
End If

'Set Workbook protection back if needed.
ActiveWorkbook.Protect Structure:=StructureProtect, Windows:=WindowProtect
End Sub

Dave

OzGrid Business Applications