Custom Watch Window?

[ParmiSS]

Is it possible to create a watch window in your own format (as shown below). The values 1-3 would be the variables, if the headings can be formatted and the window position fixed then that would be a bonus.

EBIT
Y2007 1
Y2008 2
Y2009 3

Appreciate any help.
Using Excel 2003

 

[Norie]

If you mean the Watch window in VBA then as far as I know the answer is no.

It might be possible to do something with the Windows API but I imagine that if it was it probably would be too complicated and take too long to be worth it.

What are you actually trying to achieve?

 

[ParmiSS]

Sorry i should have been clearer, i meant the Watch Window in Excel.
I have a file which models changes in profit based on changes to various inputs. What i wanted was when i make a change to one of the inputs to be able to see the changes to profit, which are contained on one particular worksheet. The workbook has about 50 sheets so navigating those is a pain and the model is used by non-finance people so presentation is also key.
I was wondering whether this can been done via a userform which is activated by an event. My VB skills are not that good (compared to the people on this site) i'm giving it a go but not 100% sure what i'm doing.

 

[Norie]

So basically you just want something to display results to the user?

Well a userform might be an option.

Could you give some more information?

When would you want to display the results?

Where will the results come from?

What user input, if any, would there be?

PS What's an Excel Watch Window? Is that something new or something I've missed? I'm still using Excel 2000.

 

[ParmiSS]

An Excel Watch Window is essentially the same as the VB one. You specify a cell and it's value is shown in the window. You can move to different worksheets and the window always remains visible. This is new to 2003 onwards.

There are 3 ranges on the worksheet "Output - PLSector" called
Ebit07
Ebit08
EBit09
these are the ranges i want visible in the window. It would probably be easier if the window is visible all the time. The inputs come from 9 worksheets and can be several 100 cells.

 

[ParmiSS]

Solution : Almost
My spreadsheet is very large and is set to manual calc, so i want the watch window to appear whenever a manual calc is performed.

Place this code in 'ThisWorkbook'
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Watch_Window.Show
End Sub

Create a userform and rename Watch_Window. This form has 3 text boxes which have been renamed lblMessage1,2 &3

Place this code in the userform.

Private Sub UserForm_Initialize()
lblMessage1.Caption = Range("Ebit07") 'Change the Label Caption
lblMessage2.Caption = Range("Ebit08") 'Change the Label Caption
lblMessage3.Caption = Range("Ebit09") 'Change the Label Caption
End Sub

Private Sub UserForm_Activate()
Me.Repaint 'Refresh the UserForm
End Sub

On the change event, which in this case is calcuation the userform will appear. Having a problem closing the userform, from other posts on this forum this may have something to do with Userform_Initialize. If anyone has a solution it would be appreciated.
I've copied the above solution from the Microsoft Knowledge Database from the "OFF97: How to Show a "Now Processing" Dialog While Macro Runs" article (id162257)