2007 code not working in 2003

[Imran J Khan]

Hi,
I know there are plenty of posts with similar issues, but I have not been
able to find one that helps me. I don't know what part of the code below is
not compatible with 2003.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ModelSheet As String
Dim R As Long

Application.ScreenUpdating = False
'Setting value for string ModelSheet according to column of active cell
Select Case ActiveCell.Offset(-1, 0).Column
Case 2
ModelSheet = "Core Model"
Case 3
ModelSheet = "Model 1"
Case 4
ModelSheet = "Model 2"
Case 5
ModelSheet = "Model 3"
Case 6
ModelSheet = "Model 4"
Case 7
ModelSheet = "Model 5"
Case 8
ModelSheet = "Model 6"
End Select
'Hidding or unhidding row where cell value is 0
R = ActiveCell.Offset(2, 0).Row
If ActiveCell.Offset(-1, 0).Value = "0" Then
Worksheets(ModelSheet).Rows(R).Hidden = True
ElseIf ActiveCell.Offset(-1, 0).Value > "0" Then
Worksheets(ModelSheet).Rows(R).Hidden = False
End If
Application.ScreenUpdating = True
End Sub

I would appreciate any input
Imran

 

[RyanH]

I don't see anything wrong. Where is the error occuring? I can point out a
few possibilities:

1.) Make sure your activecell is not is row 1. If it is then this line
will throw an error.
The error occurs because there is not a Row 0.

2.) Make sure that all ModelSheet names are located in the workbook. For
example, make sure Sheets("Core Model"), Sheets("Model 1"), etc. are located
in the workbook.

3.) Make sure the Target cell is not the last cell at the very bottom of
the worksheet.

4.) Make sure that this code is not located in one of the Sheets in the
select case statement. This may cause an error, because it is continuing to
run the code to infiinite.

5.) I changed your code slightly:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ModelSheet As String
Dim R As Long

Application.ScreenUpdating = False

' Setting value for string ModelSheet according to column of active cell
Select Case Target.Column
Case Is = 2
ModelSheet = "Core Model"
Case Is = 3
ModelSheet = "Model 1"
Case Is = 4
ModelSheet = "Model 2"
Case Is = 5
ModelSheet = "Model 3"
Case Is = 6
ModelSheet = "Model 4"
Case Is = 7
ModelSheet = "Model 5"
Case Is = 8
ModelSheet = "Model 6"
End Select

' Hidding or unhidding row where cell value is 0
R = Target.Row + 2

If Target.Offset(-1, 0).Value = 0 Then
Sheets(ModelSheet).Rows(R).Hidden = True
ElseIf Target.Offset(-1, 0).Value > 0 Then
Sheets(ModelSheet).Rows(R).Hidden = False
End If

Application.ScreenUpdating = True

End Sub

 

[Imran J Khan]

Hi Ryan,
There is no error occuring, it just does not hide the rows as it should.
This works great in my Vista/excel 2007 pc, but not on my XP/excel 2003 pc. I
need to deliver a excel 2003 compatible version.
The active cell is niether row 1 or the last work, also the sheets named are
all in the same workbook as the sheet with this code, and no, the select case
statement does not contain the name of the sheet with this code.

Your modified code has same result as the original.

Thanks for the help Ryan, any other hints?

 

[RyanH]

Everything seems to work fine for me.

Try these possibilities:

1.) Are your events disabled. Make sure your Application.EnableEvents = True

2.) Make sure the macro is located in the correct worksheet module and your
are testing the correct worksheet.

Hope this helps!

 

[Imran J Khan]

Checked to see if macro is working for right sheet/workbook. Still not
working. As for point number 1.) below, how do I enable application events?

 

[RyanH]

In the VBA Editor open the View>Immediate Window. Then type this in the
Immediate window and press Enter.

Application.EnableEvents = True

The will make sure that your Worksheet_Change Event will fire.

Now try your code again.

Hope it works for you!