Worksheet Change in Excel 97 does not run first time

[Monk]

I am using the following code to run a Macro when there is a change in cell
A1. This works well on excel 2003, however it does not run automatically on
excel 97. If the user changes the value in A1 for a second time in 97 then
the code will work. If you could provide advice on what code would work on
the first change in 97 and later versions it would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1")) Is Nothing Then
Exit Sub
Else
Application.Run "HideRows"
End If
End Sub

 

[Monk]

Unfortunately that does not solve the problem. The same isues apply. I paste
data into the spreadsheet however the macro does not run. If I then manually
delete the contents of cell A1 then the macro will run. Issue only applies to
users on 97.

 

[Peter T]

The change event works fine first time in Excel 97. Maybe there's something
else going on you haven't told us about on that leads to giving the
impression the event doesn't work first time.

Regards,
Peter T

 

[Monk]

Perhaps the issue is with the macro it is attempting to run. I have attached
the code below. Thanks for your assistance.

Sub HideRows()
'


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Sheets("AET Allocations").Select
Range("G6:G705").Select
Selection.Rows.AutoFit
Dim c As Range
Dim r As Range
For Each c In Range("A6:A800")
If c.Value = "" Then
If r Is Nothing Then
Set r = c
Else
Set r = Union(r, c)
End If
End If
Next c
r.EntireRow.Hidden = True
Range("b1").Select
Sheets("AET Client List").Select
Range("A1").Select
Selection.ClearContents
Range("a1").Select
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Peter T]

I can't see anything in your macro that would prevent the event from running
"first time". In any case the macro is called *within* then event so it
wouldn't be relevant anyway. Not sure why you are using Application.run
unless the macro is in another workbook, if not just call it directly. FWIW
here's another version of your macro -

Sub HideRows2()
Dim c As Range
Dim r As Range
Dim ws As Worksheet

On Error GoTo errExit
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

Set ws = Worksheets("AET Allocations")
ws.Range("G6:G705").Rows.AutoFit

On Error Resume Next
Set r = ws.Range("a1:a800").SpecialCells(xlCellTypeBlanks)
On Error GoTo errExit

If Not r Is Nothing Then
r.EntireRow.Hidden = False
End If

Set ws = Worksheets("AET Client List")
ws.Range("A1").ClearContents

errExit:
With Application
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub