How to prevent routine from running on certain sheets

[Gary Paris]

I have a workbook and I would like expenses totaled when I am done with
entering expenses on two seperate sheets. So far that works well. When I
enter a negative dollar amount, I have code that turns the line red and
black if the dollar amount is positive. Works OK. When I leave the two
sheets the Deactivate routine runs. When this runs, the font color is
always set to black. On my main sheet, I have some cells that have a red
background and white text. It seems that the Calc_New_Expenses routine runs
each time. How can I prevent the Calc_New_Expenses routine from running
when I select my first sheet? I don't want the text turned black.

Hope this is clear and if not, I'll try to explain better.

Thanks,

Gary




Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

If Sh.Name = "Construction Expenses" Or Sh.Name = "Misc Expenses" Then
Calc_New_Expenses
End If

End Sub


Sub Calc_New_Expenses()
'
'
Worksheets("Amounts").Range("cash_to_gary").Value = _
WorksheetFunction.SumIf(Range("Paid_By"), "Gary",
Range("Paid_By").Offset(0, -2)) _
+ WorksheetFunction.SumIf(Range("Paid_By_Misc"), "Gary",
Range("Paid_By_Misc").Offset(0, -2)) + _
WorksheetFunction.SumIf(Range("Paid_By"), "Charge",
Range("Paid_By").Offset(0, -2)) _
+ WorksheetFunction.SumIf(Range("Paid_By_Misc"), "Charge",
Range("Paid_By_Misc").Offset(0, -2))

Worksheets("Amounts").Range("cash_to_dom").Value = _
WorksheetFunction.SumIf(Range("Paid_By"), "Dom",
Range("Paid_By").Offset(0, -2)) _
+ WorksheetFunction.SumIf(Range("Paid_By_Misc"), "Dom",
Range("Paid_By_Misc").Offset(0, -2))

'
' The following code turns the row red if the dollar amount is negative
' and black if the dollar amount is positive
'
Dim MyRange As Range, MyRows As Integer, ThisRow As Integer

With ActiveSheet
Set MyRange = .UsedRange
MyRows = MyRange.Rows.Count
For ThisRow = 1 To MyRows
If IsNumeric(.Cells(ThisRow, 3)) Then
If .Cells(ThisRow, 3) < 0 Then
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Font.Color
= vbRed
Else
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Font.Color
= vbBlack
End If
End If
Next ThisRow
End With

End Sub

 

[Jim Rech]

I didn't try to run your code but it looks to me that the problem may be
that the formatting is done to the "activesheet". The sheet deactivate
event runs after you've left a sheet. So another sheet is therefore active.
You're formatting the new sheet, not the old one.

I'd try passing the sheet to Calc_New_Expenses like :

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

If Sh.Name = "Construction Expenses" Or Sh.Name = "Misc Expenses" Then
Calc_New_Expenses Sh
End If

End Sub


Sub Calc_New_Expenses(Sh as Worksheet)
Dim MyRange As Range, MyRows As Integer, ThisRow As Integer
''other code

With Sh
Set MyRange = .UsedRange
MyRows = MyRange.Rows.Count
'etc
End With

End Sub

Jim Rech
Excel MVP

 

[Gary Paris]

Jim,

This solves the problem! Thank you.

Gary