Chris,

Try this:

Sub testit()
Const vbext_ct_Document = 100
Dim vbc As Object, blnFound As Boolean

blnFound = False
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.Type = vbext_ct_Document Then
If vbc.CodeModule.CountOfLines -
vbc.CodeModule.CountOfDeclarationLines 0 Then blnFound = True
End If
Next

MsgBox IIf(blnFound, "Macros", "No Macros")
End Sub

Rob

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

I'm writing a macro that cycles through all the sheets in
another workbook detecting if there is any code behind
them. Useful if you want to quickly find out if someones
slipped in some code in a 20+ sheet model and don't fancy
clicking on each one in the VB Editor to find out...only
way I know!!

Anyway the code below is kicking out errors - starting
with user-type not defined...

This may involve making changes to the references section
in the VB Editor, but I want a solution that will work for
anyone..

Dim VBComp As Object
Dim VBCodeMod As CodeModule

For intcount = 1 To ActiveWorkbook.Sheets.count

sht_name = Sheets(intcount).Name
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sht_name).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If

Next intcount