help! Why my code can't work in excel 97

[kiwis]

Hi

I need some assistance, why my code which i create in excel 2003 can't
run in
excel 97?

General work flow of my code.

I have a raw worksheet containing my raw data & a summary worksheet
which i want to show the information for a vol number. The user will
select from a drop down list the vol number in the summary worksheet.
The vol number is unique.

My code is working in excel 2003, but when i open it in excel 97, i
can't get the output which i want when i select a unique vol number
from the drop down list.

My code

Option Explicit
Sub Worksheet_Activate()
Dim LRow As Long
Dim rng As Range
Dim rng2 As Range
Dim ws As Worksheet

Set ws = Worksheets("raw")
Set rng2 = Range("B3")
LRow = ws.Cells(Rows.count, 3).End(xlUp).Row

' Set rng = ws.Range("C2:C" & LRow)
Set rng = ws.Range("B2:B" & LRow)

'rng.Name = "cid"
rng.Name = "volser"

With rng2.Validation
.Add Type:=xlValidateList, Formula1:="=volser"
End With

End Sub

Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 2 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("summary").Range("B3").Calculate

Worksheets("raw").Range("data") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("summary").Range("B2:B3"), _
CopyToRange:=Range("A10D10"), Unique:=False

End If
End Sub

Thank you

 

[NickHK]

You should normally develop on the oldest system you intend to support, so
you do not use features that do not exist in earlier versions.
I don't have XL97 to check, but I would guess your problem lies with either
the .AdvancedFilter or .Validation.
You need to test on XL97.

NickHK

 

[Peter T]

There's nothing wrong with the code. Not sure why the default Private has
been removed from the sheet events but that's not an issue.

I notice though you have a validation list. Unlike in later versions, in
Excel 97 changes applied from a DV list do not trigger a Change event.

Regards,
Peter T

 

[Harlan Grove]

There's nothing wrong with the code. Not sure why the default Private has
been removed from the sheet events but that's not an issue.

I notice though you have a validation list. Unlike in later versions, in
Excel 97 changes applied from a DV list do not trigger a Change event.

....

But entries from DV lists do trigger Calculate events.

 

[Peter T]

...

But entries from DV lists do trigger Calculate events.

Only indirectly if linked to a formula in another cell, eg =A1 where A1 is
the DV cell. Indeed this is the normal suggested workaround for Excel 97,
but there are others too.

Regards,
Peter T