Updating Status Bar message when totals change

[mcomp72]

I am writing a Macro workbook with many sheets on it. On one sheet (let's call it Sheet1) is a total, and that total is generated from numbers located on many other sheets in the workbook.

I want the total on Sheet1 to display in the Status Bar, so I can see what it currently is no matter what sheet I'm currently working on. So, it's important that the Status Bar "refresh" every time the total changes, so it is always showing me the most current total.

I was able to figure out how to get the total to show on the Status Bar when the workbook opens, but am having a very hard time doing so when the sheets are updated. I put some code in the Worksheet_Change sub of Sheet1 (since that is where the total resides), but I started getting a 400 error, and at one point I was given some other error over and over again, until Excel crashed. (Unfortunately I didn't write down the error # and now I can't remember.) I can't understand what I could have done that caused such a problem.

I would like to ask the hive mind here: if you needed to do what I am trying to do, what is the way you would go about it? I must be making it way more difficult that it needs to be, because I have seen other workbooks that do what I am trying to do. I just can't seem to wrap my head around how to do it. I would love to see the way some of you would tackle this.

 

[footoo]

Re: Need help on updating Status Bar message when totals change

Normal module:

Sub TTL()
Dim tot As Double
tot = Sheets("Sheet1").[A10]
Application.DisplayStatusBar = True
Application.StatusBar = "Current Total : " & Format(tot, "#,##0.00")
End Sub

Sheet1 module:

Private Sub Worksheet_Calculate()
Call TTL
End Sub

 

[mcomp72]

Re: Need help on updating Status Bar message when totals change

That seems to be working great! Thank you!!!