Cell value in status bar

[jchavez76]

Hello, I'm new to this forum and fairly new to excel. However, I've taken 3 excel classes, through an advanced class so I'm fairly literate now.

I think vba will be what I need to solve this request. I'd like to show the value of a cell (although it would probably be best to define it as a range, in case the cell moves) in the status bar. I did find this thread that shows how to do it. I did it and it works, but a few strange things are happening: http://www.mrexcel.com/forum/showthread.php?t=427782

For reference, I am using excel to build bids for a construction company and I'd like to be able to keep an eye on the grand total regardless of where I am in the spreadsheet.

First of all, everything else is gone from the status bar, which I'd like to remain. Secondly, the value does not update when the bid total changes. Third, I'd like for the vba script to run automatically when the file is opened.

Any help is very much appreciated.

Thanks,

Jacob Chavez, building analyst.

 

[Norie]

Couldn't you just use a section of the worksheet for the total and freese panes so that part is always visible?

 

[jchavez76]

It just wouldn't be as elegant and user friendly for a number of reasons, but for 1, there are several "dials" that are changed on different worksheets that affect the project total, so the ability to see if from any worksheet is really nice.

 

[djreiswig]

Private Sub Worksheet_Calculate()
     Application.Caption = "Project Total: $" & Range("total").value & " Projected Profit: $" & Range("profit").value & " Construction Duration: " & Range  ("days").Value
End Sub

You were close. You have to put an & symbol between each piece of data. The text is a piece of data and the value is also a piece of data.

 

[Norie]

Surely if you put all the statistics/metrics on a worksheet more formatting options would be available.

To be honest I don't think most people that use Excel don't expect to look at either the caption or status bar for this type of information.

Also by changing the caption/status bar you might lose the information that they usually contain.

You could also end up with some strange captions if more than one workbook is open.

 

[jchavez76]

Thanks dj, for some reason, it's is still returning a bug the error # is 1004, and says "Method 'Range' of object '_Worksheet' failed. Is this because the cell for "profit" is on a different worksheet?

The name of the worksheet that it's on is "Labor, Overhead & Profit"

Jacob