Why does this COUNT macro not work

[velohead]

Hi All,

I have two macros, the first macro works perfectly, the second macro does not work at all (error message).
What is wrong with the second macro, please.

Sub This_Works_For_Workbook()
MsgBox ActiveWorkbook.Styles.Count
End Sub
Sub This_Does_Not_Work_For_Worksheet()
MsgBox ActiveSheet.Styles.Count
End Sub

The macro is supposed to count styles used, as excel has a limit of 64,000 styles else the "TOO MANY DIFFERENT CELL FORMATS" message pops up when attempting yet another font size (for example).
Yes, I have inherited a file with too many cell formats, Doh!
I just want to count the cell formats in one worksheet, so I know which to give priority.

If someone has the time to list worksheets with a style count, I would not dissuade them, although I do not expect that of course.

Thank You All

 

[jmacleary]

Hello there

Unfortunately there is no styles object for the worksheet object, only for the workbook object, hence why it doesnt work.

 

[velohead]

Simple. Thanks for replying.

 

[Trebor76]

You could loop through each tab copying each to a new workbook, counting the styles and then closing the newly created workbook without saving. Seems a lot of work though.

 

[velohead]

It's a possibility that I had not considered.

Seems a lot of work - but would extend my VBA knowledge though.