I guess add code in your routine to see if there is really code detected or
blank spaces - ie, check for the offending condition.

This would require getting the lines and doing a trim, then checking the
length - haven't thought out the possibilities, however.

--
Regards,
Tom Ogilvy

"Chris Gorham" wrote in message
...
Good Call..
it appears that even if there is no code, but there is a
blank line created by simply pressing return, then this
will be detected as macro code.
any work arounds..??

Chris

-----Original Message-----
If you get such a false report, then if you go into each
module and do
ctrl+A, then delete and close the module.

then rerun, do you get a false report?

--
Regards,
Tom Ogilvy

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

yes, Dave Peterson's code works fine - but even he
admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
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



.



.