Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change event problems
I am creating budget templates that use the worksheet_change event to modify
formulas in other cells of the sheet. One sheet is for budget and other is for salaries. On the budget sheet everything works like it should, on the salary sheet the cell unlock is ignored and the update traps to my error routine. The column contains a drop-down validation that I am testing. Is there something I am missing? Here is a portion of the code that works on one sheet but not the other: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not (Target.Interior.ColorIndex = 39 Or Cells(Target.Row, 4).Value = "Specific") Then Exit Sub ' On error restore operation of this code, then disable events till we are done here On Error GoTo ErrHandler Application.EnableEvents = False ' Process each of the changed value in succession For Each rng In Target ' Setup the formulas based on the selected spreading means If rng.Column = 4 Then Select Case rng.Value Case "n/a" ' No Formulas for this row With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Value = 0 .Locked = True End With Case "Even" ' Split the amount evenly across the year With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Formula = "=$C" & rng.Row & "/12" .Locked = True End With Case "Front Qtr" ' Split the amount in the 1st month of each quarter For i = 1 To 12 With rng.Offset(0, i) .Locked = False If i Mod 3 = 1 Then .Formula = "=C" & rng.Row & "/4" Else .Value = 0 End If .Locked = True End With Next i |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change event problems
Hi Ken,
Firstly can you comment out the On Error GoTo ErrHandler and then tell us on what line the code is failing. Next you said that you have drop down data validation. Does the data you are entering in the cell meet the data validation criteria? -- Regards, OssieMac "Ken Schobloher" wrote: I am creating budget templates that use the worksheet_change event to modify formulas in other cells of the sheet. One sheet is for budget and other is for salaries. On the budget sheet everything works like it should, on the salary sheet the cell unlock is ignored and the update traps to my error routine. The column contains a drop-down validation that I am testing. Is there something I am missing? Here is a portion of the code that works on one sheet but not the other: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not (Target.Interior.ColorIndex = 39 Or Cells(Target.Row, 4).Value = "Specific") Then Exit Sub ' On error restore operation of this code, then disable events till we are done here On Error GoTo ErrHandler Application.EnableEvents = False ' Process each of the changed value in succession For Each rng In Target ' Setup the formulas based on the selected spreading means If rng.Column = 4 Then Select Case rng.Value Case "n/a" ' No Formulas for this row With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Value = 0 .Locked = True End With Case "Even" ' Split the amount evenly across the year With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Formula = "=$C" & rng.Row & "/12" .Locked = True End With Case "Front Qtr" ' Split the amount in the 1st month of each quarter For i = 1 To 12 With rng.Offset(0, i) .Locked = False If i Mod 3 = 1 Then .Formula = "=C" & rng.Row & "/4" Else .Value = 0 End If .Locked = True End With Next i |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change event problems
Hi OssieMac,
The drop down data validation selects a method of spreading an anual budget (i.e. evenly accross the 12 months, quarterly, specific). The value is selected by the user from the drop down list, this is working correctly. The program code works fully on the first (budget sheet). The same code applied to the salary sheet fails. All of the cells that I want to modify with the event are locked and the sheet is password protected with UserInterfaceOnly option. With error trapping off, stepping through the code, the .Locked = False does not uplock the cell, but returns no error. The next line .value = 0 sends me to a UDF that calculates payroll taxes, skipping any remaining code in the event. Calling the UDF would be expected as the cell with the drop down is a dependent argument. Thanks, Ken "OssieMac" wrote: Hi Ken, Firstly can you comment out the On Error GoTo ErrHandler and then tell us on what line the code is failing. Next you said that you have drop down data validation. Does the data you are entering in the cell meet the data validation criteria? -- Regards, OssieMac "Ken Schobloher" wrote: I am creating budget templates that use the worksheet_change event to modify formulas in other cells of the sheet. One sheet is for budget and other is for salaries. On the budget sheet everything works like it should, on the salary sheet the cell unlock is ignored and the update traps to my error routine. The column contains a drop-down validation that I am testing. Is there something I am missing? Here is a portion of the code that works on one sheet but not the other: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not (Target.Interior.ColorIndex = 39 Or Cells(Target.Row, 4).Value = "Specific") Then Exit Sub ' On error restore operation of this code, then disable events till we are done here On Error GoTo ErrHandler Application.EnableEvents = False ' Process each of the changed value in succession For Each rng In Target ' Setup the formulas based on the selected spreading means If rng.Column = 4 Then Select Case rng.Value Case "n/a" ' No Formulas for this row With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Value = 0 .Locked = True End With Case "Even" ' Split the amount evenly across the year With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Formula = "=$C" & rng.Row & "/12" .Locked = True End With Case "Front Qtr" ' Split the amount in the 1st month of each quarter For i = 1 To 12 With rng.Offset(0, i) .Locked = False If i Mod 3 = 1 Then .Formula = "=C" & rng.Row & "/4" Else .Value = 0 End If .Locked = True End With Next i |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change event problems
Hi Ken,
I am afraid that I am lost in the logic of what you are doing. Worksheet_Change event is called if any cell on the worksheet is changed. Your code does not identify what range has changed and this event can be called if only one cell is changed. (Application.Intersect is usually used to test if changed cell falls withing the required range for processing to continue.) For Each rng In Target This indicates a number of cells are in the range Target. What changes a number of cells simultaneously to trigger the event and then have Target as a range to use the For Each....? Also any change you make to a cell withing the event code will cause the event to be called again. Seems to me that it should have Application.EnableEvents = False as the first line of the code and then turn events back on at the end of the sub. If using Application.EnableEvents = False then you should have a little sub like the following that you can run from the VBA editor if your code fails during testing otherwise the events remain turned off. Sub ReEnable_Events() Application.EnableEvents = True End Sub Perhaps you have not posted all the code. Also, when using identical code for multiple sheets the usual practice is to place the code in a module and simply call the sub from the event routine like the following. then when you have it working it should work with all worksheets. Private Sub Worksheet_Change(ByVal Target As Range) 'maybe If code here to identify if required cell has changed call MySub("SheetName") end sub Then in the module: Sub MySub(shtName as string) 'code here end sub -- Regards, OssieMac "OssieMac" wrote: Hi Ken, Firstly can you comment out the On Error GoTo ErrHandler and then tell us on what line the code is failing. Next you said that you have drop down data validation. Does the data you are entering in the cell meet the data validation criteria? -- Regards, OssieMac "Ken Schobloher" wrote: I am creating budget templates that use the worksheet_change event to modify formulas in other cells of the sheet. One sheet is for budget and other is for salaries. On the budget sheet everything works like it should, on the salary sheet the cell unlock is ignored and the update traps to my error routine. The column contains a drop-down validation that I am testing. Is there something I am missing? Here is a portion of the code that works on one sheet but not the other: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not (Target.Interior.ColorIndex = 39 Or Cells(Target.Row, 4).Value = "Specific") Then Exit Sub ' On error restore operation of this code, then disable events till we are done here On Error GoTo ErrHandler Application.EnableEvents = False ' Process each of the changed value in succession For Each rng In Target ' Setup the formulas based on the selected spreading means If rng.Column = 4 Then Select Case rng.Value Case "n/a" ' No Formulas for this row With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Value = 0 .Locked = True End With Case "Even" ' Split the amount evenly across the year With Range("E" & rng.Row & ":P" & rng.Row) .Locked = False .Formula = "=$C" & rng.Row & "/12" .Locked = True End With Case "Front Qtr" ' Split the amount in the 1st month of each quarter For i = 1 To 12 With rng.Offset(0, i) .Locked = False If i Mod 3 = 1 Then .Formula = "=C" & rng.Row & "/4" Else .Value = 0 End If .Locked = True End With Next i |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change Event | Excel Programming | |||
Worksheet_Change event | Excel Programming | |||
How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
Worksheet_Change event | Excel Programming | |||
Worksheet_Change Event | Excel Programming |