Using Workbook_SheetChange to create a custom status bar message with calculations

[Huhenyo]

I've been trying to come up with a way to display some calculations for the users of my workbook, but without having to take up space in the workbook itself. The solution I think I've figured out is to use the statusbar, and I think this might be a helpful idea for others to use. I saw an example where a single calculation was put into the status bar which is where I got the idea of putting multiple custom calculations into the bar. My calculations are going to be much more elaborate than in this example, but to keep things simple, this is what I have put into my Workbook code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.StatusBar = "Custom Calc1: " & Application.Evaluate("=sum(T5:T8)") & _
    "  Custom Calc2: " & Application.Evaluate("=sum(T9:T12)") & "  Custom Calc2: " & _
    Application.Evaluate("=Average(T5:T12)")
End Sub

I really only want this to update when an item in a certain column is changed, but I'm not sure how to set that up.

Question 1: Would it be a bad idea to just let it update with every little change of the workbook?
Question 2: I get the following error, the first time this thing fires "User-defined type not defined", but then it works fine after that. What am I don't wrong that I get this error?
Question 3: The workbook I want this code in is created by a macro from another workbook. How do I have that macro put this code into the new workbook (either right as it is created, or ideally, at the end of the macro from the original workbook)? - Had a hard time searching for answers on this because I get so many hits on how to create a new workbook itself and I already know how to do that.

Thank you all for your help. You are all so helpful and I really appreciate it.

 

[royUK]

How many peoplw would actually think to look for the result in the StatusBar? Excel performs calculations automatically when a range containing numbers is selected and places the results at the bottom screen. Not many people notice this feature.

Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.StatusBar = ""
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
On Error Resume Next
    Application.StatusBar = "Custom Calc1: " & Application.Evaluate("=sum(T5:T8)") & _
    "  Custom Calc2: " & Application.Evaluate("=sum(T9:T12)") & "  Custom Calc2: " & _
    Application.Evaluate("=Average(T5:T12)")
    On Error GoTo 0
End Sub

I have put the error handler in becuase an error occurs if ther are no numbers to calculate. You may want to exclude certain sheets or check if the range for the Sums actually contains numbers.

 

[royUK]

I forgot to say that this line limits the action to changes in Column E

If Target.Column <> 5 Then Exit Sub

I also used the Workbook_SheetSelectionChange

The SheetActivate event clears the Statusbar when each sheet is activated

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.StatusBar = ""
End Sub