Worksheet Type Question 1

[RonRepp]

Hi, all:

I have a procedure that asks for the name and type of a worksheet when I run through the sheets object and I get err 438 "Object Doesn't support this property..."

Sub countsheets()
'For i = 1 To Sheets.Count
'    MsgBox Sheets(i).Name '& vbCrLf & Worksheets(i).Name & vbCrLf & Worksheets(i).Type
'Next
For Each sh In ActiveWorkbook.Sheets
    sh.Activate
    MsgBox ActiveSheet.Name & vbCrLf & ActiveSheet.Type
Next

End Sub

If I use Worksheets, it's fine for type, but does not count a chart, DLG or Macro. If I use the sh object, it counts them all but errs on the type for charts,DLG or Macro. (I was trying ActiveSheet @ the end to see if that made a difference.

I'm trying to find the Type because other code will do something w/the sheet dependent on type.

thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[SkipVought]

Hi,

Try this...

Sub countsheets()
    Dim sh As Object
    On Error Resume Next
    For Each sh In ActiveWorkbook.Sheets
        Debug.Print sh.Name
        Debug.Print sh.Type
        If Err.Number <> 0 Then
            Debug.Print Err.Description
            Err.Clear
        End If
        Debug.Print
    Next

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RonRepp]

Skip:

Once again you're my hero (why didn't I think of it?). This was the printout:

Dialog1
Object doesn't support this property or method

Macro1
 3 

Chart1
 3 

Sheet1
-4167 

Sheet2
-4167 

aaa
-4167

So, now I know the error is on the DLG sheet, so I can allow for it.

thanks again,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[SkipVought]

Just as I allowed for the error that occured in this code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}