Is there a collection that holds all sheets: work- chart- dialog-

[JohnKauffman]

Is there a collection that holds all sheets: work- chart- dialog- ?
What would be the DIM datatype of a member? (none of the intellitype options seem to fit)
Also, when I add the use of the dialog() collection VBA says it can't recognize the whole sub()
Tester I am using

Sub CollectionsSheet()
' ? what collection holds all sheets: work-, chart- dialog-

Dim wsSheetCurrent As Worksheet
Dim wsChartCurrent As Chart
Dim dlgDialogCurrent As Dialog

For Each wsSheetCurrent In Worksheets()
MsgBox "Worksheet object in WorkSheets collection: " & Chr(13) & wsSheetCurrent.Name
Next wsSheetCurrent

For Each wsChartCurrent In Charts()
MsgBox "Chart object in Sheets collection: " & Chr(13) & wsChartCurrent.Name
Next wsChartCurrent

' ??? when using dialogs() VBA says entire sub() not recognized
'For Each dlgDialogCurrent In Dialogs()...
' MsgBox dlgDialogCurrent.Name
'Next dlgDialogCurrent
End Sub

 

[Norie]

Perhaps Sheets is the collection you want.

 

[JohnKauffman]

Thanks Norie
Sheets() fails when enumerates to a chart sheet.
Perhaps wrong DIM data type for a member?

' fails when enumerates to first chart sheet
Dim wsSheetCurrent As Worksheet
For Each wsSheetCurrent In Sheets()
MsgBox "Worksheet object in Sheets collection: " & Chr(13) & wsSheetCurrent.Name
Next wsSheetCurrent

 

[Firefly2012]

As you are iterating thru the Sheets collection, you need a variable that can refer to all types of sheets. Hence, you can Dim sht As Object (or equivalent variable name):

Dim sht As Object
For Each sht In Sheets()
MsgBox "Sheet object in Sheets collection: " & Chr(13) & sht.Name
Next sht