Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Status bar not visible in my .xlsx worksheets. | Excel Discussion (Misc queries) | |||
Running a macro for all visible worksheets in a workbook | Excel Discussion (Misc queries) | |||
Macro for making worksheets visible. | Excel Programming | |||
Help: Macro to to sum across selected visible worksheets | Excel Programming | |||
Visible property on Worksheets | Excel Programming |