Custom Watch Window?

ParmiSS

New Member
Joined
Apr 28, 2005
Messages
5
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 28, 2005
Messages
5
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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.:oops:
 

ParmiSS

New Member
Joined
Apr 28, 2005
Messages
5
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

New Member
Joined
Apr 28, 2005
Messages
5
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)
 

Forum statistics

Threads
1,141,758
Messages
5,708,358
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top