Cell value in Title Bar or System Tray -Possible?

[Guest]

Oh wise ones,

Is it possible to display the value of a cell in the Excel title bar or the
system tray. You can see what I'm getting at, monitoring the value of a cell
while excel is minimized. This value is constantly changing every 2 seconds
from a sheet calculation macro, so the Title/Tray would have to change
dynamically.

Mike

 

[Jim Rech]

It should be easy to update Excel's title bar. Just right click your
worksheet's tab and pick View Code. Then paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Caption = Target.Value
End Sub

 

[Guest]

Jim,
I was afraid of this. I works if I manually change the value in the
active cell, but this cell changes based on a sheet recalculation, not a
change event. The intial value is displayed and remains static. Any other
sugestions that may work with a worksheet calculation event? This value is
the speed of a manufacturing line pulled from the sql server every 2 seconds,
so it's basically realtime.

Thanks,
Mike

 

[Guest]

How about using the on time event in a recursive call...

Sub RefreshTitle()
MsgBox "Tada"
application.caption = Sheets("Sheet1").Range("A1").value
Application.OnTime Now + TimeValue("00:00:02"), "RefreshTitle"

End Sub

 

[evgny]

Hi Mike
I dont now where I fond this, but you could modifi it.

put this in ThiWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Stops the clok
On Error Resume Next
Application.OnTime NextSecond, "Update", , False
End Sub

Private Sub Workbook_Open()
Update
End Sub

Put this in a module.
Option Explicit
Dim NextSecond As Variant
Sub Update()

ThisWorkbook.Sheets(1).Calculate
' update every second
NextSecond = Now + TimeValue("00:00:01")
Application.OnTime NextSecond, "Update"
End Sub

Regards
Yngve

 

[Guest]

Jim T,
It doesn't work at all. I can't even get it to update manually.
Is it still due to the calculated vs change event? I'm not that strong in
VBA but I do know this sheet has given me fits in other areas due to the
calculate function.

Thanks a bunch,
Mike

 

[Guest]

Evgny,
Thanks for the reply. Let me clarify though. I already have a
sheet that updates every 2 seconds and displays the production line speed in
cell D2. I wanted to able to see the value of that cell in the Windows
taskbar or tray. If I can get it displayed in the excel title bar I can view
it in the taskbar even if I'm in another app. The problem is that the line
constantly flucuates and even stops. So the excel titlebar has to be updated
dynamically so I can see a stopped production line while I'm in, say
Outlook, and respond.

Thanks,
Mike

 

[Guest]

On Time is it's own thing. You do not want to have it in the on calculate or
on change or any other event. What that function I gave you does is it

Posts a message box (Just to prove it is working.)
Changes the application caption
Sets up to call itself again in two seconds.

This code must be placed in a standard module and not in a sheet or in
thisworkbook.

 

[Jim Rech]

If _another_ cell changes I assume that triggers the change event. But
first the sheet should recalc.

So...
1. Some cell changes
2. Sheet recalcs due to automatic calc mode
3. Our change event handler is called due to #1

Revised code to handle #3:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Caption = Range("A1").Value
End Sub

Change A1 to the cell you want to see the value for.

Another option is to put the same code as above in calc event handler:

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

 

[Guest]

SWEEET!

Thanks Jim. I added the one line "Application.caption...." to my existing
refresh code and it works like a champ!

Have a great new year,

Mike

 

[Guest]

Jim,
I ended up using the second option and it works like a charm.
Thanks so much for your help.

Mike

 

[Guest]

Global variables persist for the duration as do static varaibles. I think you
are familiar with globals so try this code and run it twice...

Sub test()
Static x As Integer

MsgBox x
x = 10
MsgBox x
End Sub

the first msgbox is a zero and the rest of the time they will be 10.