Display Number of Additional Open Excel Windows in a Cell

[Bruce Johnson]

In Excel 2002 is there a way to have a cell display the number of additional
open excel windows or, at least indicate that there are additional windows
open?

Thank you for responding.

Bruce Johnson

 

[Frank Kabel]

Hi Bruce
maybe the following UDF will do what you want (counts the open
workbooks):
Public Function count_wbks() as integer
count_wbks = application.workbooks.count
end function

Now you can put the following formula in a cell:
=count_wbks()

 

[Bruce Johnson]

Hi Frank



Thanks for replying.

I did as you suggested but still have a problem.



The =count_wbks formula only works if it is entered (into a cell) after a
change is made in the number of workbooks open.



Example:

With only one workbook open, entering the formula produces a 1 in the cell.

When I open additional workbooks the number does not change.

Doing a recalculation does not help.

However, if I copy the formula to a different cell, the copied formula then
gives the correct number.



I wrote a macro to do a copy operation every 5 seconds but that makes it
very difficult to do anything else in the spreadsheet.



Any additional suggestions?



Best regards,

Bruce Johnson

Rockford, Ill USA

 

[Frank Kabel]

Hi
try
Public Function count_wbks() as integer
application.volatile
count_wbks = application.workbooks.count
end function

 

[Bruce Johnson]

Hi again,

Adding "application.volatile" did the trick.
Opening additional workbooks now automatically changes the number as
desired.
The number does not change when workbooks are closed until there is a
calculation.
This should not be a problem.

Thank you very much for your help and have a great day,
Bruce