If I want this code to execute upon opening Excel:
Option Explicit
Public WithEvents mApp As Application
Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim vAvg As Variant 'to account for errors
Dim lCells As Long
Dim lCnt As Long
Dim vMax As Variant
Dim vMin As Variant
Dim vSum As Variant
Dim dCnta As Double
'Make sure selection is a range
If TypeName(Target) = "Range" Then
'Only when more than one cell is selected
If Target.Cells.Count > 1 Then
'Caclulate stats
vAvg = Application.Average(Target)
lCells = Target.Cells.Count
lCnt = Application.Count(Target)
vMax = Application.Max(Target)
vMin = Application.Min(Target)
vSum = Application.Sum(Target)
dCnta = Application.CountA(Target)
'Concatenate statusbar message
Application.StatusBar = "Average: " & CStr(vAvg) & " | " & _
"Cell Count: " & lCells & " | " & _
"Count Nums: " & lCnt & " | " & _
"CountA: " & dCnta & " | " & _
"Max: " & CStr(vMax) & " | " & _
"Min: " & CStr(vMin) & " | " & _
"Sum: " & CStr(vSum) & " | "
Else
'Return control of statusbar
Application.StatusBar = False
End If
Else
Application.StatusBar = False
End If
End Sub
put it as a class module in personal.xls named SheetStatsClass
Then, have a normal code module in personal.xls include:
Public x As New SheetStatsClassSub InitializeApp() Set x.mApp = Application End Sub
Finally, have this code in ThisWorkbook in personal.xls:
Private Sub Workbook_Open() InitializeApp End Sub
0 Responses to “Excel - getting an application event to fire when starting Excel”
Leave a Reply