Excel - getting an application event to fire when starting Excel

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”


  1. No Comments

Leave a Reply