Keep small window always on top of main workbook

[xtapalat]

I am running a budget model schools through excel in which there are multiple tabs into which I enter the inputs (e.g. #of students enrolled, maintenance cost/square foot, english department budget, etc.) and a single tab on which the output budget is calculated and summarized. The challenge with working with any budget is calibrating the assumptions so that the output tab is ultimately balanced.

To help me do that, what I want is to be able to create a small window that displays my output tab and have it always be on top in the upper righthand corner of my screen as I am working through the input tabs. That way, as I am changing the individual assumptions, I can tell immediately what the impact is on the bottomline and whether the budget is in or out of balance.

I can create the new window (View/New Window), resize it, and move it to the top right of the screen easily. The problem I am having is getting it to stay on top even when I am working on a different window (in my case the main excel window that is displaying all of the assumptions tabs). Any thoughts on how I would do that?

Thanks,
Bryan

 

[tweedle]

This will be tricky as you are asking Excel to modify how it maintains window focus, which ultimately affects what receives any given command.

I suppose if you're cloning your view window as described, then you could do something like

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each Window In Application.Windows
    With Window
        If Right(.Caption, 2) = ":2" Then
        Window.Activate
        End If
    End With
Next
End Sub

Assuming that the :2 window is the summary, though the switching window focus may get irritating during data input.

The other path is to create a non-modal userform and push your summary values to that.

 

[xtapalat]

Thanks, Tweedle. I think what I'm going to want to do is create the non-modal user form. I've begun researching how to do that and might post back if I run into obstacles. From what I've read so far, it looks like the spreadsheets control would do the trick and I just need to figure out the mechanics.

Appreciate you pointing me in the right direction.

-Bryan

 

[VoG]

How about the Camera tool http://processtrends.com/pg_charts_camera_tool.htm

 

[xtapalat]

Thanks. I'd never used the camera tool before. It's great. The one drawback for what I'm trying to do is that it doesn't seem to be able to float. As I scroll down the inputs page, the camera window I've created moves up the screen and eventually out of site. While I was trying to figure out how to use the camera tool, I came across a function called the Watch Window (Formulas/Formula Auditing). It does float in upper righthand corner of my screen and lets me enter specific cells in the workbook whose value I want to track. It's not perfect in that it displays a lot of information I don't care about, but it is a crude way to accomplish what I'm trying to do without much work. If there is a way to make the camera tool float, I'd love to hear it. I'll also continue reseaching how to create the custom user form.

Best,
Bryan