Geoff,

Since Offset is a reserved Excel word I wonder if that's causing a problem?
You might try changing it.

Doug

"Geoff" wrote in message
...
I've written the following into the sheet module of a workbook that will
be
accessed by a lot of users who have Excel 97. I have both Excel 2003 and
97
on my machine, and this code runs fine in both versions. However, there is
a
problem with one of the users (using Excel 97), who has come back with the
error 'VBA Compile error: Automation error', and the header of the
Cell_Comment sub highlighted. Can anyone see what might be causing this?

Private Sub Worksheet_Calculate()

Dim cell As Range
Dim msg As String

Application.ScreenUpdating = False

Me.Unprotect Password
For Each cell In Me.Range("O9:O499")
msg = "Total minutes is not 0. Please enter a"
msg = msg & " value in the" & vbLf & "Actual Sitting Days column."
Call Cell_Comment(cell, msg, -1)
Next

For Each cell In Me.Range("X9:X499")
msg = "Sitting Days is not 0. Please enter a"
msg = msg & " value in the" & vbLf & "Sitting Time column."
Call Cell_Comment(cell, msg, 1)
Next

For Each cell In Me.Range("Y9:Y499")
msg = "Sitting Time is not 0. Please enter a"
msg = msg & " value in the" & vbLf & "Sitting Days column."
Call Cell_Comment(cell, msg, -1)
Next
Me.Protect Password

Application.ScreenUpdating = True

End Sub

Private Sub Cell_Comment(ByVal cell As Range, msg As String, offset As
Integer)
'
' Sets cell Comment to value msg if condition is met, deletes otherwise.
'
If cell.offset(0, offset).Value < 0 Then
If cell.Value = "" Then
If cell.Comment Is Nothing Then
With cell.AddComment(msg)
.Visible = True
End With
End If
Else
If Not cell.Comment Is Nothing Then _
cell.Comment.Delete
End If
Else
If Not cell.Comment Is Nothing Then _
cell.Comment.Delete
End If
End Sub

TIA

--
There are 10 types of people in the world - those who understand binary
and
those who don't.