Does anyone know why the following code works fine in Excel 2000 but not at
all in Excel 2002 SP3? I need to hide or show different columns depending on
whether the selection made from a validation list in F1 is Hi-Low or EDLC.
This file needs to be worked on by users with either version of Excel.
Help?!! This is driving me nuts!

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

End Sub

Hi
do you get an error in Excel 2003 or what exactly dows not work?

--
Regards
Frank Kabel
Frankfurt, Germany

"cottage6" schrieb im Newsbeitrag
...
Does anyone know why the following code works fine in Excel 2000 but
not at
all in Excel 2002 SP3? I need to hide or show different columns
depending on
whether the selection made from a validation list in F1 is Hi-Low or
EDLC.
This file needs to be worked on by users with either version of
Excel.
Help?!! This is driving me nuts!

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

End Sub

Are you sure the user enabled macros in xl2003?



cottage6 wrote:

Does anyone know why the following code works fine in Excel 2000 but not at
all in Excel 2002 SP3? I need to hide or show different columns depending on
whether the selection made from a validation list in F1 is Hi-Low or EDLC.
This file needs to be worked on by users with either version of Excel.
Help?!! This is driving me nuts!

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

End Sub

--

Dave Peterson

The following works for me in Excel XP, SP3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim S As Long
Dim Visibility As Boolean

If Target.Address = "$F$1" Then
Visibility = (UCase$(Target.Value) = "EDLC")
Worksheets("Period 1").Columns("M:O").Hidden = Visibility
For S = 2 To 12
Worksheets("Period " & Format$(S)).Columns("K:M").Hidden = Visibility
Next S
End If
End Sub

Are you sure events are enabled in the Excel 2002 workbook? Do you have other
event macros? Do they fire?

On Thu, 28 Oct 2004 13:45:03 -0700, "cottage6"
wrote:

Does anyone know why the following code works fine in Excel 2000 but not at
all in Excel 2002 SP3? I need to hide or show different columns depending on
whether the selection made from a validation list in F1 is Hi-Low or EDLC.
This file needs to be worked on by users with either version of Excel.
Help?!! This is driving me nuts!

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

End Sub

Thanks to Mryna, Frank, and Dave for their answers. The code Myrna sent also
does not work for me, and it does not look like the event macro is firing. I
can clearly see that it does when I test it in Excel 2000. I do not have any
other event macros; Auto Open and Close macros that work fine. When you ask
if the events are enabled do you mean when you're asked whether or not you
want to enable macros when first opening a file? If so, the answer is yes
they are enabled. Is there something else I need to do to enable events? I
did see a reference to Application.EnableEvents = True in Help, but that
didn't make a difference. I'm fairly inexperienced when I get a problem like
this. To answer Frank's question, if I step this macro I get an "object
required" error on the first "If Target.Address = "$F$1" And Target.Value =
"Hi-Low" Then" line of the code.

"Myrna Larson" wrote:

The following works for me in Excel XP, SP3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim S As Long
Dim Visibility As Boolean

If Target.Address = "$F$1" Then
Visibility = (UCase$(Target.Value) = "EDLC")
Worksheets("Period 1").Columns("M:O").Hidden = Visibility
For S = 2 To 12
Worksheets("Period " & Format$(S)).Columns("K:M").Hidden = Visibility
Next S
End If
End Sub

Are you sure events are enabled in the Excel 2002 workbook? Do you have other
event macros? Do they fire?

On Thu, 28 Oct 2004 13:45:03 -0700, "cottage6"
wrote:

Does anyone know why the following code works fine in Excel 2000 but not at
all in Excel 2002 SP3? I need to hide or show different columns depending on
whether the selection made from a validation list in F1 is Hi-Low or EDLC.
This file needs to be worked on by users with either version of Excel.
Help?!! This is driving me nuts!

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

End Sub

Thanks again for the help, but I did get this working finally. I did not
know about enabling events so I learned a very valuable lesson. I must have
added the line of code in the wrong place when I first tried it. All is okay
now.

"Myrna Larson" wrote:

The following works for me in Excel XP, SP3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim S As Long
Dim Visibility As Boolean

If Target.Address = "$F$1" Then
Visibility = (UCase$(Target.Value) = "EDLC")
Worksheets("Period 1").Columns("M:O").Hidden = Visibility
For S = 2 To 12
Worksheets("Period " & Format$(S)).Columns("K:M").Hidden = Visibility
Next S
End If
End Sub

Are you sure events are enabled in the Excel 2002 workbook? Do you have other
event macros? Do they fire?

On Thu, 28 Oct 2004 13:45:03 -0700, "cottage6"
wrote:

Does anyone know why the following code works fine in Excel 2000 but not at
all in Excel 2002 SP3? I need to hide or show different columns depending on
whether the selection made from a validation list in F1 is Hi-Low or EDLC.
This file needs to be worked on by users with either version of Excel.
Help?!! This is driving me nuts!

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

End Sub