Excel 97 workbook open events

wwbwb

Well-known Member
Joined
Oct 20, 2003
Messages
513
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???

Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You have this line in the code but never qualify any of the range references using it.
Code:
With Sheets("Reminders")
Also there's an argument in the sort that is not available in 97.

Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
Code:
 With Sheets("Reminders") 
    
        Columns("A:B")
This refers to columns A & B on sheet Reminders.
Code:
 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?
 
Upvote 0
Ah... I see. thanks! I will make sure I qualify everything better and give it another try. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top