ActiveWorkbook.Sheets.Count

[Guest]

Hi everyone,

When I execute the code: "ActiveWorkbook.Sheets.Count", I get 10 while there
are only 9. The workbook and the sheets are not password protected and there
are no hidden sheets either.

I am using Excel XP and Windows XP.

What could the problem be?

 

[Guest]

A little more on this question...

I figured that there is a difference between "Sheets" and "Worksheets". Ok.
But, now, there are 10 sheets and 9 worksheets. So where is the 10th sheet?
How do I display it or, if this is not possible, how do I get to know what it
is? What type of sheet?

Thanks.

 

[Guest]

Jac,

You probably have a Very Hidden Sheet.

Go into the VBE. In the project window every worksheet in the workbook will
be listed here. Find the one that does not have a worksheet tab in Excel.
Back in VBE, in the project window, click on the missing Sheet. Now look at
the Properties window, the Visible attribute is either xlSheetHidden or
xlSheetVeryHidden. Change it to xlSheetVisible.

 

[Guest]

Hi Yankee 428,

I checked this out and this does not seem to be the case. The only sheet
that is not listed in VBE is ThisWorkbook.

I wrote a macro to list all the sheet names in a message box:

Sub NbSheets()
Dim intI As Integer
Dim strMessage As String
strMessage = ""
MsgBox "Nb sheets: " & ActiveWorkbook.Sheets.Count & vbCrLf & _
"Nb worksheets: " & ActiveWorkbook.Worksheets.Count
' I get 9 and 10.
For intI = 1 To ActiveWorkbook.Sheets.Count
strMessage = strMessage & intI & "- " & _
Sheets(intI).Name & vbCrLf
Next intI
MsgBox strMessage
End Sub

In the resulting message, I got a name that is not in the worksheets list:
"Module". I cannot see it in the worksheets list. There are no very hidden
sheets. There are 7 forms and 7 code modules, one of which is named "Module".

So what could it be?

Thank you for your concern.

 

[Dave Peterson]

Maybe this will help isolate your trouble:

Option Explicit

Sub testme01()
Dim sh As Object
Dim iCtr As Long

iCtr = 0

For Each sh In ActiveWorkbook.Sheets
iCtr = iCtr + 1
MsgBox iCtr & vbLf & sh.Name & vbLf & TypeName(sh)
Next sh

End Sub

 

[Guest]

Hi Dave,

With your little macro, I found the problem. In a particular workbook, one
sheet is of type "Module" while all the others are of type "Worksheet". So to
prevent my macro to get an error, I will only process the worksheets. But I
wander why this is possible. There are other modules in that workbook and
they are not listed in the sheets list. There are user forms to... Do you (or
anyone) have an idea why and how this happened?

Thanks

 

[Dave Peterson]

Did that workbook start as xl95?

Try running Rob Bovey's code cleaner against your workbook--it exports, deletes
modules and reimports your code. (you can also do it manually)

I'd try running Rob Bovey's codecleaner:
http://www.appspro.com/


And see what happens next time you run that little macro.

===
xl95 had code on module sheets. xl97 introduced the VBE.

Hi Dave,

With your little macro, I found the problem. In a particular workbook, one
sheet is of type "Module" while all the others are of type "Worksheet". So to
prevent my macro to get an error, I will only process the worksheets. But I
wander why this is possible. There are other modules in that workbook and
they are not listed in the sheets list. There are user forms to... Do you (or
anyone) have an idea why and how this happened?

Thanks

 

[Guest]

Hi Dave,

Yes, I think it did. As a matter of fact, it must have. That is a good
point. I will effectively try Rob's code cleaner on those workbooks (there
are 60 of them) that cause problem.

That is a very interesting comment. I really appreciate.

Thank you Dave.