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.

 

[VoG]

I don't think that you can preserve the existing information from the status bar and still write to it. How about writing the data to the title bar?

Right click a sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Application.Caption = Target.Value
End Sub

then try changing the value of A1 and see what is displayed in the title bar.

If that works OK for you, what is the real range of interest and does that cell contain a formula or do you update it manually?

 

[jchavez76]

Yeah, that would be fine. I did as you said, but (please excuse my rookie question here) I don't know how to run the script. I thought I had hit the green arrow (run sub/user form) before, but this seems to open the macro dialog box.

Thanks for your quick reply to my first inquiry!

Jacob

 

[VoG]

This is event code that runs automatically. Just change the value in A1 and see the title bar change.

 

[jchavez76]

Alright! I see the value there now. I didn't realize it was there, then when I closed the file and when I reopened it, the value wasn't there. So, I started a new file and did what you said and there it was.

So, then I closed the file and save it, reopened it and it wasn't there anymore. If I override the value in the cell, it appears. Now I realize why you were asking the question on how data gets into this cell. It is automatically calculated as the sum of all of the values above it.

So, I guess I need some way for this code to update automatically. Let me know if you can think of any way to do this.

Thanks,
Jacob

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Application.Caption = Range("A1").Value
End Sub

Change A1 to the correct cell reference. This will update automatically.

 

[p45cal]

Also check out the Watch Window - this is really what it's for. See:
http://office.microsoft.com…
or
http://www.techrepublic.com/…

You can also use the camera tool - it's a live picture of the cell, see:
http://chandoo.org/wp/2008/12/02/excel-camera-tool-help/ and/or:
http://spreadsheetpage.com/index.php/oddity/the_camera_tool/ and or:
http://processtrends.com/pg_charts_camera_tool.htm

You can then format the picture a bit to make it stand out, change its size, then you could add a bit of code to the sheet's module along the lines of:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.Shapes("Picture 19")
  .Left = Columns(ActiveWindow.ScrollColumn).Left + 10
  .Top = Rows(ActiveWindow.ScrollRow).Top + 10
End With
End Sub

Of course, you could put it into a worksheet_calculate event, or a worksheet_change event instead.
Doing it this way means you can include several (contiguous) cells at once within the picture.

 

[jchavez76]

Nice, thanks so much. It's working great. Is there any way to put "Project Total: $" in front of it? I'm going to be rolling this out to others in my company and you know how people are, they won't get it unless it point blank says it.

I appreciate your help on this very much.

Jacob

 

[djreiswig]

Private Sub Worksheet_Calculate()
Application.Caption = "Project Total: $" & Range("A1").Value
End Sub

Change the cell reference to meet your needs.

 

[jchavez76]

Great, thanks again. The reason that I don't want to use the watch window is because it takes up so much real estate on the screen. Plus, not all users would use it and it's really valuable to see the bid total at all times.

So, if I were to expand upon this and add other information into the code, could someone help me understand what the coding means? For instance, after "Project Total: $12,345" I may want to put the number of days and the projected profit (both of which I have cells in the workbook where these values are calculated. I've tried tweaking the code myself such as the following and it seems to have a bug.

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

Thanks

Jacob