Worksheet_Change help please
 

My spreadsheet is tracking budget vs acutals for various stages in a project.
Column E stores the Project stage, Initiation, Planning and Execution.
Columns H through J store original budget, revised budget and actual for the
Initiation Stage, Columns K through M store original budget, revised budget
and actual for the Planning stage, Columns N through P store original budget,
revised budget and actual for the Execution Stage, Column Q remaining budget
and the grand total is stored in Column S.

Depending on what is selected in Column E, the total (column S) is updated.

This is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Target, Range("E:E"))

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case (.Value)
Case "Initiation":
'if there is a value in the revised budget column (4) use it
and add any remaining budget column (12) to the total
'otherwise use the original budget column (3) + any
remaining budget column (12)
If .Offset(0, 4).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 4).Value + .Offset(0,
12).Value
Else
.Offset(0, 13).Value = .Offset(0, 3).Value + .Offset(0,
12).Value
End If
Case "Planning":
'if there is a value in the revised budget for planning
column (7) use it as well as the actual cost
'for the previous stage column (5) Plus any remaining
budgetcolumn (12) in the total column (13)
If .Offset(0, 7).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
7).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
6).Value + .Offset(0, 12).Value
End If
Case "Execution":
'if there is a value in the revised budget for execution
column use it as well as the actual cost
'for the previous stage(s) in the total
If .Offset(0, 10).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 10).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 9).Value + .Offset(0, 12).Value
End If
Case Else:
'do nothing. Allow the entry in the E column but don't
bother with any other updates
End Select
End With
End If

XIT:
Application.EnableEvents = True

End Sub

-------------------------------------------------------------------
This works great.

Problem is, if I make any updates in any of the Columns H through Q, I have
to reselect Column E of the same row to get the code to fire.

I'm not quite sure how to update my code to have it trigger if any changes
occur in the Columns H through Q (as well as E) and then evaluate whats in
Column E and do the appropriate Calculation for the total budget.

Any guidance would be greatly appreciated.

Thanks

Worksheet_Change help please
 

it may just be

change this

Set rng = Intersect(Target, Range("E:E"))

to

Set rng = Intersect(Target, Range("E:E;H:Q"))


"Beans" wrote:

My spreadsheet is tracking budget vs acutals for various stages in a project.
Column E stores the Project stage, Initiation, Planning and Execution.
Columns H through J store original budget, revised budget and actual for the
Initiation Stage, Columns K through M store original budget, revised budget
and actual for the Planning stage, Columns N through P store original budget,
revised budget and actual for the Execution Stage, Column Q remaining budget
and the grand total is stored in Column S.

Depending on what is selected in Column E, the total (column S) is updated.

This is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Target, Range("E:E"))

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case (.Value)
Case "Initiation":
'if there is a value in the revised budget column (4) use it
and add any remaining budget column (12) to the total
'otherwise use the original budget column (3) + any
remaining budget column (12)
If .Offset(0, 4).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 4).Value + .Offset(0,
12).Value
Else
.Offset(0, 13).Value = .Offset(0, 3).Value + .Offset(0,
12).Value
End If
Case "Planning":
'if there is a value in the revised budget for planning
column (7) use it as well as the actual cost
'for the previous stage column (5) Plus any remaining
budgetcolumn (12) in the total column (13)
If .Offset(0, 7).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
7).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
6).Value + .Offset(0, 12).Value
End If
Case "Execution":
'if there is a value in the revised budget for execution
column use it as well as the actual cost
'for the previous stage(s) in the total
If .Offset(0, 10).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 10).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 9).Value + .Offset(0, 12).Value
End If
Case Else:
'do nothing. Allow the entry in the E column but don't
bother with any other updates
End Select
End With
End If

XIT:
Application.EnableEvents = True

End Sub

-------------------------------------------------------------------
This works great.

Problem is, if I make any updates in any of the Columns H through Q, I have
to reselect Column E of the same row to get the code to fire.

I'm not quite sure how to update my code to have it trigger if any changes
occur in the Columns H through Q (as well as E) and then evaluate whats in
Column E and do the appropriate Calculation for the total budget.

Any guidance would be greatly appreciated.

Thanks

Worksheet_Change help please
 

Hi Patrick, thanks for your suggestion, but I get a Run-Time Error '1004'
Method 'Range' of object'_Worksheet' failed.

Any suggestions.

Cheers

"Patrick Molloy" wrote:

it may just be

change this

Set rng = Intersect(Target, Range("E:E"))

to

Set rng = Intersect(Target, Range("E:E;H:Q"))


"Beans" wrote:

My spreadsheet is tracking budget vs acutals for various stages in a project.
Column E stores the Project stage, Initiation, Planning and Execution.
Columns H through J store original budget, revised budget and actual for the
Initiation Stage, Columns K through M store original budget, revised budget
and actual for the Planning stage, Columns N through P store original budget,
revised budget and actual for the Execution Stage, Column Q remaining budget
and the grand total is stored in Column S.

Depending on what is selected in Column E, the total (column S) is updated.

This is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Target, Range("E:E"))

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case (.Value)
Case "Initiation":
'if there is a value in the revised budget column (4) use it
and add any remaining budget column (12) to the total
'otherwise use the original budget column (3) + any
remaining budget column (12)
If .Offset(0, 4).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 4).Value + .Offset(0,
12).Value
Else
.Offset(0, 13).Value = .Offset(0, 3).Value + .Offset(0,
12).Value
End If
Case "Planning":
'if there is a value in the revised budget for planning
column (7) use it as well as the actual cost
'for the previous stage column (5) Plus any remaining
budgetcolumn (12) in the total column (13)
If .Offset(0, 7).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
7).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
6).Value + .Offset(0, 12).Value
End If
Case "Execution":
'if there is a value in the revised budget for execution
column use it as well as the actual cost
'for the previous stage(s) in the total
If .Offset(0, 10).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 10).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 9).Value + .Offset(0, 12).Value
End If
Case Else:
'do nothing. Allow the entry in the E column but don't
bother with any other updates
End Select
End With
End If

XIT:
Application.EnableEvents = True

End Sub

-------------------------------------------------------------------
This works great.

Problem is, if I make any updates in any of the Columns H through Q, I have
to reselect Column E of the same row to get the code to fire.

I'm not quite sure how to update my code to have it trigger if any changes
occur in the Columns H through Q (as well as E) and then evaluate whats in
Column E and do the appropriate Calculation for the total budget.

Any guidance would be greatly appreciated.

Thanks

Worksheet_Change help please
 

very similar to the previous responsePrivate Sub
Worksheet_Change(ByVal Target As Range)
Dim rng2 As Range
Set rng2 = Intersect(Target, Range("E:E,H:Q"))
If Not rng2 Is Nothing Then
Dim rng As Range
Set rng = Intersect(Target.EntireRow, Range("E:E"))
If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case (.Value)
Case "Initiation":
'if there is a value in the revised budget column (4) use it
and add any remaining budget column (12) to the total
'otherwise use the original budget column (3) + any remaining
budget column (12)
If .Offset(0, 4).Value 0 Then
Offset(0, 13).Value = .Offset(0, 4).Value + .Offset(0,
12).Value
Else
Offset(0, 13).Value = .Offset(0, 3).Value + .Offset(0,
12).Value
End If
Case "Planning":
'if there is a value in the revised budget for planning
column (7) use it as well as the actual cost
'for the previous stage column (5) Plus any remaining
budgetcolumn (12) in the total column (13)
If .Offset(0, 7).Value 0 Then
Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
7).Value + .Offset(0, 12).Value
Else
Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
6).Value + .Offset(0, 12).Value
End If
Case "Execution":
'if there is a value in the revised budget for execution
column use it as well as the actual cost
'for the previous stage(s) in the total
If .Offset(0, 10).Value 0 Then
Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 10).Value + .Offset(0, 12).Value
Else
Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 9).Value + .Offset(0, 12).Value
End If
Case Else:
'do nothing. Allow the entry in the E column but don't bother
with any other updates
End Select
End With
End If
End If
XIT:
Application.EnableEvents = True
End Sub
but with a bit to ensure rng refers to column E.
Also, consider what happens if someone pastes a range of values with
more than one row. The rng range could be a column of cells in column E
and you would probably want to process all of them.
PerhapsPrivate Sub Worksheet_Change(ByVal Target As
Range)
Dim rng2 As Range
Set rng2 = Intersect(Target, Range("E:E,H:Q"))
If Not rng2 Is Nothing Then
Dim rng As Range
Set rng = Intersect(Target.EntireRow, Range("E:E"))
If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
For Each cll In rng.Cells
With cll
Select Case (.Value)
Case "Initiation":

Worksheet_Change help please
 

Thank you very much.... that did the trick.

Cheers

"p45cal" wrote:


very similar to the previous responsePrivate Sub
Worksheet_Change(ByVal Target As Range)
Dim rng2 As Range
Set rng2 = Intersect(Target, Range("E:E,H:Q"))
If Not rng2 Is Nothing Then
Dim rng As Range
Set rng = Intersect(Target.EntireRow, Range("E:E"))
If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case (.Value)
Case "Initiation":
'if there is a value in the revised budget column (4) use it
and add any remaining budget column (12) to the total
'otherwise use the original budget column (3) + any remaining
budget column (12)
If .Offset(0, 4).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 4).Value + .Offset(0,
12).Value
Else
.Offset(0, 13).Value = .Offset(0, 3).Value + .Offset(0,
12).Value
End If
Case "Planning":
'if there is a value in the revised budget for planning
column (7) use it as well as the actual cost
'for the previous stage column (5) Plus any remaining
budgetcolumn (12) in the total column (13)
If .Offset(0, 7).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
7).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
6).Value + .Offset(0, 12).Value
End If
Case "Execution":
'if there is a value in the revised budget for execution
column use it as well as the actual cost
'for the previous stage(s) in the total
If .Offset(0, 10).Value 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 10).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 9).Value + .Offset(0, 12).Value
End If
Case Else:
'do nothing. Allow the entry in the E column but don't bother
with any other updates
End Select
End With
End If
End If
XIT:
Application.EnableEvents = True
End Sub
but with a bit to ensure rng refers to column E.
Also, consider what happens if someone pastes a range of values with
more than one row. The rng range could be a column of cells in column E
and you would probably want to process all of them.
PerhapsPrivate Sub Worksheet_Change(ByVal Target As
Range)
Dim rng2 As Range
Set rng2 = Intersect(Target, Range("E:E,H:Q"))
If Not rng2 Is Nothing Then
Dim rng As Range
Set rng = Intersect(Target.EntireRow, Range("E:E"))
If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
For Each cll In rng.Cells
With cll
Select Case (.Value)
Case "Initiation":