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("A10:DD10"), Unique:=False

End If
End Sub

Thank you

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

"kiwis" wrote in message
oups.com...
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("A10:DD10"), Unique:=False

End If
End Sub

Thank you

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

"kiwis" wrote in message
oups.com...
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("A10:DD10"), Unique:=False

End If
End Sub

Thank you

"Peter T" <peter_t@discussions wrote...
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.

"Harlan Grove" wrote in message
"Peter T" <peter_t@discussions wrote...
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.

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