Compile error: Automation error in Excel 97
 

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.

Compile error: Automation error in Excel 97
 

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.

Compile error: Automation error in Excel 97
 

Thanks Doug

I've just now managed to pin it down - I had kept in a reference to a
calendar control which wasn't available to Excel 97 users. Lesson
learned...-_-

I've changed the code as you suggest though, that may well cause problems
also.

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


"Doug Glancy" wrote:

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.

Compile error: Automation error in Excel 97
 

Glad you found the problem.

Doug

"Geoff" wrote in message
...
Thanks Doug

I've just now managed to pin it down - I had kept in a reference to a
calendar control which wasn't available to Excel 97 users. Lesson
learned...-_-

I've changed the code as you suggest though, that may well cause problems
also.

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


"Doug Glancy" wrote:

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.