If you change your references to type Object as Dave Peterson did in your
second example, you won't need a reference to the Visual Basic Extensibility
library as recommended by Rob Bovey (and which you apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to invest a little time in
understanding it and figuring out why it might appear to not provide what
you need.

--
Regards,
Tom Ogilvy

"Chris Gorham" wrote in message
...
Hi,

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in the
references section of the VB Editor...

This solution creates an error "user type not defined" for
objComponent....

Sub CheckForDocObjectCode()
Dim objComponent As VBIDE.VBComponent
For Each objComponent In
ActiveWorkbook.VBProject.VBComponents
If objComponent.Type = vbext_ct_Document Then
If objComponent.CodeModule.CountOfLines 0
Then
MsgBox objComponent.Name & " has code."
Else
MsgBox objComponent.Name & " does not have
code."
End If
End If
Next objComponent
End Sub

this solution runs, but ocassionally seems to detect lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

Dim VBCodeMod As Object 'As CodeModule
Dim intCount As Long
Dim Sht_name As String
Dim macro As String

For intCount = 1 To ActiveWorkbook.Sheets.Count

Sht_name = Sheets(intCount).CodeName
Set VBCodeMod _
= ActiveWorkbook.VBProject.VBComponents
(Sht_name).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that have
provided the above code, no criticism of their expertise
is intended...Chris