Display Number of Additional Open Excel Windows in a Cell

  • Thread starter Thread starter Bruce Johnson
  • Start date Start date
B

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
 
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()
 
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
 
Hi
try
Public Function count_wbks() as integer
application.volatile
count_wbks = application.workbooks.count
end function
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top