| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 code not working in 2003
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2007 code not working in 2003
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. If ActiveCell.Offset(-1, 0).Value = "0" Then 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 -- Cheers, Ryan "Imran J Khan" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2007 code not working in 2003
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" wrote: 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. If ActiveCell.Offset(-1, 0).Value = "0" Then 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 -- Cheers, Ryan "Imran J Khan" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2007 code not working in 2003
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! -- Cheers, Ryan "Imran J Khan" wrote: 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" wrote: 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. If ActiveCell.Offset(-1, 0).Value = "0" Then 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 -- Cheers, Ryan "Imran J Khan" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2007 code not working in 2003
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" wrote: 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! -- Cheers, Ryan "Imran J Khan" wrote: 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" wrote: 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. If ActiveCell.Offset(-1, 0).Value = "0" Then 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 -- Cheers, Ryan "Imran J Khan" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2007 code not working in 2003
In the VBA Editor open the ViewImmediate 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! -- Cheers, Ryan "Imran J Khan" wrote: 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" wrote: 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! -- Cheers, Ryan "Imran J Khan" wrote: 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" wrote: 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. If ActiveCell.Offset(-1, 0).Value = "0" Then 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 -- Cheers, Ryan "Imran J Khan" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Charts from 2003 not working in 2007 | Excel Discussion (Misc queries) | |||
| VISTA working with OFFICE 2003,2007 both or which? | Excel Worksheet Functions | |||
| 2003--2007 recognize if i'm in 2007 or 2003 via code. | Excel Programming | |||
| Working Between Excell 2003 & 2007 | Excel Worksheet Functions | |||
| Formulas in 2007 not working in 2003 | Excel Worksheet Functions | |||
Linear Mode
