Thanks for this - however I'm still battling to get it to work in Excel 2003.
I've tried both at home and at work but with no luck, it won't detect the
number of lines.
any chance you could try this in 2003 yourself...?? Also could I ask why you
wanted to detect "option" and what the "left$" (as opposed to "left")
achieves...

Thks...Chris

"RB Smissaert" wrote:

It works fine in Excel 2002.
I would alter the funtion a bit:

Private Function test_macro(Sht_name As String)

Dim totalLines As Long
Dim beg_line As Long
Dim test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then
For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
If Len(test_line) 0 And Left$(test_line, 6) < "Option" Then
test_macro = True
Exit Function
End If
Next beg_line
End If

test_macro = False

End Function


RBS


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

I'm writing some code that detects if there are lines of macro code behind
a
sheet in an Excel workbook. The following function returns true if it
detects
code and it works fine for Excel 2000...but for some reason it doesn't
seem
to work for Excel 2003. I've checked the libraries and as far as I can see
I've brought the same ones in, although two refer to Office & Excel 11.0
rather than 9.0 (if that makes a difference). For the record somewhere out
there is the individual who supplied this code to me in the first
place...to
whom I'm grateful

All suggestions welcome....Rgds Chris

Private Function test_macro(Sht_name As String)

Dim totalLines As Long, beg_line As Long, test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then

For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
Next beg_line

End If

If test_line = "" Then
test_macro = False
Else
test_macro = True
End If

End Function