Worksheet Change Event - not working in XP

[Eva Shanley]

I got a response to the following post on Friday from Dave
Peterson who stated this code worked for him in Excel
2002. I just had one of our techs try this code in 2002
and it didn't work for him either. I have Excel 2002
(10.6501.6626) SP3; does anyone know if the version is
making a difference in this code not working?

The following code works fine in Excel 2000, but will not
work in XP. I'm getting an "object required" error on
line 3; "If Target.Address = "$E$1" Then". Thanks for any
help on this.

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ws As Worksheet
If Target.Address = "$E$1" Then
If Target.Value _
= "Hi-Low" Then
For Each ws In Sheets(Array("Period 1", "Period
2", "Period 3", "Period 4", "Period 5", "Period 6", _
"Period 7", "Period 8", "Period 9", "Period 10", "Period
11", "Period 12"))
ws.Columns("M:O").EntireColumn.Hidden = False
Next ws

End If
If Target.Value _
= "EDLC" Then
For Each ws In Sheets(Array("Period 1", "Period
2", "Period 3", "Period 4", "Period 5", "Period 6", _
"Period 7", "Period 8", "Period 9", "Period 10", "Period
11", "Period 12"))
ws.Columns("M:O").EntireColumn.Hidden = True
Next ws


End If
End If
End Sub

 

[Don Lloyd]

Hi,

The code fragment worked for me - Excel 2003 SP1
I seem to recall having a somewhat similar problem and replacing it with

If Target.Row = 1 and Target.Column = 5 Then
.. . .etc

No further problems.

regards,
Don.

 

[Dave Peterson]

If you create a new workbook with just enough info to compile this code, does it
work in xl2002 SP3?

I wouldn't think that it was a reuse of a variable, but do you use Target
anywhere in your code (as a variable).

And this too, doesn't seem to fit (but it'll give you something to look at):

Inside the xl2002 SP3 VBE (for this project), look under tools|references. Do
you see any marked Missing.

(I've only seen broken functions--never properties from this kind of error.)

 

[Dave Peterson]

A couple of thoughts.

Maybe you have an invalid character on that line in your code. Maybe
deleting that line and retyping would help.

And sometimes exporting the module, deleting the module and
reimporting the module can clean up unexplainable errors.

In fact, Rob Bovey has an addin that will do that for you.

Rob Bovey's codecleaner:
http://www.appspro.com/