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" <(E-Mail Removed)> wrote in message
> news:BE6C4023-82B4-4DC2-85C2-(E-Mail Removed)...
> > 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.
>
>
>
|
Similar Threads
