Excel 97 workbook open events

[wwbwb]

The following code works just fine when running Excel 2002. It is called duing a workbook_open event.

When I run it as a macro in Excel 97 it works also.

But... when I have it called during a workbook_open event in Excel 97, it runs the code (just fine) and when it tries to finish, then Windows(XP) says "ugh uh" and shuts down excel asking me to send/not send and debug. If I try to debug to find out what is wrong... nothing happens. If I don't have this called during the workbook_open event, everything is just perfect. Anyone have any ideas???

Sub reminder()
Application.ScreenUpdating = False

Dim dates As Date
Dim LastRow As Long

With Sheets("Reminders")

    Columns("A:B").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A2").Select

dates = Date

LastRow = Range("a65536").End(xlUp).Row
For Each c In Range("a2:a" & LastRow)
If c = dates Then
MsgBox c.Offset(0, 1), , "Reminder for " & c
End If
If c < dates Then
response = MsgBox("The following entry is past due." & Chr(13) & c & " - " & c.Offset(0, 1) & _
            Chr(13) & "Do you want to delete it?", vbYesNo, "Past Due")
            If response = vbYes Then
            c.EntireRow.ClearContents
            Else
            End If
End If
Next c

End With
End sub

 

[Norie]

You have this line in the code but never qualify any of the range references using it.

With Sheets("Reminders")

Also there's an argument in the sort that is not available in 97.

Sub reminder()
Application.ScreenUpdating = False
Dim c As Range
Dim dates As Date
Dim LastRow As Long

    With Sheets("Reminders")
    
        .Columns("A:B").Sort Key1:=.Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
    
        dates = Date
    
        LastRow = .Range("a65536").End(xlUp).Row
        
        For Each c In .Range("a2:a" & LastRow)
            If c = dates Then
                MsgBox c.Offset(0, 1), , "Reminder for " & c
            End If
            
            If c < dates Then
                response = MsgBox("The following entry is past due." & Chr(13) & c & " - " & c.Offset(0, 1) & _
                Chr(13) & "Do you want to delete it?", vbYesNo, "Past Due")
                If response = vbYes Then
                    c.EntireRow.ClearContents
                End If
                    
            End If
        
        Next c
    
    End With
End Sub

 

[wwbwb]

Norie:

Thanks. I knew about the DataOption1:=xlSortNormal that showed up the instant I ran it on the 97 computer, and had removed it. I had copied the code from the 2002 computer and forgot to remove it when I posted.

What do you mean "qualify any of the range references"

...and if that were the case, why does it work on 02 and not 97?

 

[Norie]

Well when you use a with statement with a sheet you need to use the dot(.) qualifier to reference ranges on the sheet.

If you don't VBA will refer to the range on the active sheet.

eg

This refers columns A & B on the active sheet.

 With Sheets("Reminders") 
    
        Columns("A:B")

This refers to columns A & B on sheet Reminders.

 With Sheets("Reminders") 
    
        .Columns("A:B")

I don't know why the code wouldn't work in 97, the only version specific thing I can see is the DataOption1.

Perhaps for some reason when the workbook is opened in 97 the wrong sheet is active?

 

[wwbwb]

Ah... I see. thanks! I will make sure I qualify everything better and give it another try. Thanks.