determining the Visible value of a worksheet

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

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

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