Is there a way to create a pop-up window in Excel?

[Donald]

I would like to create a pop-up window by clicking on one cell while working
on one sheet which will cause a window to open which displays a range of
cells which are located on another sheet in the same workbook and I would
like to be able to access the cells in that window. A very simple example
would be to access a range of cells that simply =A1*B1*C1=D1 where the
formula is in cell D1 and I can enter the data in cells A1, B1, and C1 in the
pop-up window. The function of the range of cells accessed through the
pop-up would be more complex than the simple example given. I just want to
be able to access the range of cells and make use of all of their functions
via the pop-up window.

Hopefully, this is possible and someone knows how. Thank you for your help.

 

[Gord Dibben]

Window>New Window

Now you have two windows workbook.xls:1 and workbook.xls:2

Arrange and size as you need. Select which sheet and/or range of cells to
be in each window.

You could use code to hide and unhide the second window as you like.

Sheet1 in window 1

Sheet2 in window 2

In Sheet1 module enter this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
With Windows("workbook.xls:2")
.Visible = Not .Visible
End With
Cancel = True
End Sub

Double-click on a cell in Sheet1 window will toggle Sheet2 window hidden or
visible.


Gord Dibben MS Excel MVP

 

[Donald]

We were able to make this work which is great. Thank you. Now, if you can
tell me whether or not it would be possible to expand on this and have more
than one pop-up window from the same sheet. What I would like is to have the
pop-up window be cell specific meaning if I double click on a certain cell,
such as cell A1, a pop-up window tied to that cell would open and if I double
click on another cell, such as A9, a different pop-up window tied to that
cell would open. I might have ten or fifteen or even more different cells
which I would like to have tied to different pop-up windows. Is this
possible? Again, thank you for your help.

 

[Gord Dibben]

It is possible using the same method but will become a little unwieldy with
a dozen windows being hidden and unhidden.

I guess I must ask now why you feel you need this feature?

You can double-click on a cell that has references to other cells on a
separate sheet and be taken to those cells on the other sheet.

You must go into Options and uncheck "edit directly in cell"

If you have a formula in Sheet1 A1 like =SUM(Sheet2!F5:F10) and

double-click on A1 you will go to Sheet2 with F5:F10 selected.

Is that something you could live with?


Gord

 

[Donald]

I have been experimenting with your proposed solution and yes, I believe it
does accomplish what I need well enough. I have several cells on several
different sheets in a workbook which are using ranges of cells on other
sheets to calculate the response I see in each of those cells and just as you
indicate, by unchecking "Allow editing directly in cells" I can go directly
to that range of cells on the other sheet by double clicking on the cell.
Seems like such a simple solution. I plan to use a cell near that range of
cells to create a means to toggle back to the other sheet as well. I
certainly wish I knew more about the many things the software can accomplish.
Thank you once again.

Since you did say it was possible to accomplish the first solution you gave
me with two separate windows, but to have many separate windows, if you have
time can you tell me how that would be accomplished. I would like to both
learn a little about that one more thing and to experiment with that solution
as well. If it is too much work or requires too much time I am satisfied
with the second solution suggested.

I have and use a commercially available construction cost estimating
software program. With that software one of the many features I notice is
that one can just scroll from cell to cell on the main estimate spreadsheet
and have a separate window open which displays many items of information
which were used to calculate the answer in the cell on the main spreadsheet.
As one scrolls from cell to cell the information in the second window changes
to the information linked to that cell. Even the cells in the second window
can access yet further into the database. I am sure that is only possible
with special programming, but that is more or less what I am trying to
accomplish with Excel.

 

[Gord Dibben]

Toggling 15 Windows hidden/unhidden depending upon d-click on a cell is too
much time and effort right now.

I will suggest you have a look at the Formula Auditing features of Excel.

Specifically the Trace Precedents and Trace Dependents.

When you enable trace precedents arrows you just double-click on the arrow
and Goto dialog box pops up.

Select the precedent or one of the precedents if multiple and OK.

Now you are on a precedent cell and can edit that cell.

Show trace dependents arrow and d-click on arrow to get Goto dialog box.

Pick dependent and OK. Now you are back to original formula.

You can also utilize the Watch Window to see results changing as you alter
formula precedents.


Gord