Hi,
I'm new to VBA. I just need to do one very simple thing. I have an Excel file with QA test cases, and I want to be able to type P for pass or F for fail (or alternatively change the color to Green or Red), then have Excel automatically recalculate the number of tests that passed/failed and update the cells that contain those totals. I can get it to run if I manually tell it to run the function, but not automatically when a cell is changed.
Here is what I have so far:
Module1:
CEventMod:
As you can see, I've tried several events (even though I think I only need the Change event). What else do I need to do?
I'm new to VBA. I just need to do one very simple thing. I have an Excel file with QA test cases, and I want to be able to type P for pass or F for fail (or alternatively change the color to Green or Red), then have Excel automatically recalculate the number of tests that passed/failed and update the cells that contain those totals. I can get it to run if I manually tell it to run the function, but not automatically when a cell is changed.
Here is what I have so far:
Module1:
Code:
Option Explicit
Public xlApp As New CEventMod
Public Sub TrapAppEvents()
Set xlApp.App = Application
End SubCEventMod:
Code:
Option Explicit
Public WithEvents App As Application
'Define these in each Sub.
Dim WHITE As Integer ' = 0
Dim RED As Integer ' = 3
Dim GREEN As Integer ' = 4
Public Sub App_PassFail(ByVal Sh As Object, ByVal Target As Range)
MsgBox ("App_PassFail() was called.")
Dim numPass As Integer
Dim numFail As Integer
Dim numEmpty As Integer
Dim row As Integer
WHITE = 0
RED = 3
GREEN = 4
For row = 3 To 23
Cells(row, 3).Select
If Selection.Interior.ColorIndex = GREEN Then
numPass = numPass + 1
ElseIf Selection.Interior.ColorIndex = RED Then
numFail = numFail + 1
Else
numEmpty = numEmpty + 1
End If
Next row
'Now display the results.
Cells(3, 5).Value = numPass & " Tests Pass"
Cells(3, 5).Font.Bold = True
Cells(3, 5).Interior.ColorIndex = GREEN
Cells(4, 5).Value = numFail & " Tests Fail"
Cells(4, 5).Font.Bold = True
Cells(4, 5).Interior.ColorIndex = RED
Cells(5, 5).Value = numEmpty & " Tests Not Tested"
Cells(5, 5).Font.Bold = True
Cells(5, 5).Interior.ColorIndex = WHITE
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox ("App_SheetActivate() was called.")
Dim Target As Range
Call App_PassFail(Sh, Target)
End Sub
Private Sub App_SheetCalculate(ByVal Sh As Object)
MsgBox ("App_SheetCalculate() was called.")
Dim Target As Range
Call App_PassFail(Sh, Target)
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ("App_SheetChange() was called.")
Call App_PassFail(Sh, Target)
End Sub
Private Sub App_SheetDeactivate(ByVal Sh As Object)
MsgBox ("App_SheetDeactivate() was called.")
Dim Target As Range
Call App_PassFail(Sh, Target)
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ("App_SheetChange() was called.")
Call App_SheetSelectionChange(Sh, Target)
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox ("App_Workbook_Open() was called.")
WHITE = 0
RED = 3
GREEN = 4
End Sub
