Visible WorkSheets
 

I need to know how many worksheets are set to ".visible"
in my workbook.

Can you tell me how to do that ,and, also how to list them
using VBA.


Thanks

CharlesW

Visible WorkSheets
 

Hi Charles

Try this

Sub test()
'xlSheetVisible = -1
Dim sh As Worksheet
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
N = N + 1
' Cells(N, 1).Value = sh.Name
' you can list them on the activesheet like this
End If
Next
With ThisWorkbook
msgbox "There are " & N & " Visible worksheets of the " _
& .Worksheets.Count & " Worksheets"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



" wrote in message ...
I need to know how many worksheets are set to ".visible"
in my workbook.

Can you tell me how to do that ,and, also how to list them
using VBA.


Thanks

CharlesW

Visible WorkSheets
 

Charles,

You can also change the line

If sh.Visible = -1 Then

to be

If sh.Visible = xlSheetVisible Then

which is a bit more descriptive, or even

If sh.Visible Then


--
HTH

Bob Phillips

"Ron de Bruin" wrote in message
...
Hi Charles

Try this

Sub test()
'xlSheetVisible = -1
Dim sh As Worksheet
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
N = N + 1
' Cells(N, 1).Value = sh.Name
' you can list them on the activesheet like this
End If
Next
With ThisWorkbook
msgbox "There are " & N & " Visible worksheets of the " _
& .Worksheets.Count & " Worksheets"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



" wrote in message
...
I need to know how many worksheets are set to ".visible"
in my workbook.

Can you tell me how to do that ,and, also how to list them
using VBA.


Thanks

CharlesW

Visible WorkSheets
 

Ron / Bob


This code works perfectly.


Thanks


CharlesW



"Ron de Bruin" wrote in message
...
Hi Charles

Try this

Sub test()
'xlSheetVisible = -1
Dim sh As Worksheet
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
N = N + 1
' Cells(N, 1).Value = sh.Name
' you can list them on the activesheet like this
End If
Next
With ThisWorkbook
msgbox "There are " & N & " Visible worksheets of the " _
& .Worksheets.Count & " Worksheets"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



" wrote in message
...
I need to know how many worksheets are set to ".visible"
in my workbook.

Can you tell me how to do that ,and, also how to list them
using VBA.


Thanks

CharlesW