Hi Chris,

Here's a bit better approach to the problem. All of this type of code
requires a reference to the Visual Basic for Applications Extensibility
object library. This object library is forward compatible, but not backward
compatible, so you need to compile and save this in the earliest version of
Excel that you plan to have it run in. If the user is running Excel 2002 or
higher, they must also check the Trust Access to Visual Basic Project
checkbox under the Tools/Macro/Security/Trusted Sources menu.

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

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"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