I am trying to write a simple code within an existing macro that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder

Hi Hervinder,

Try:

MsgBox Application.Workbooks.Count


---
Regards,
Norman



"Hervinder" wrote in message
...

I am trying to write a simple code within an existing macro that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder

Use Workbooks.Count


Hervinder wrote:
I am trying to write a simple code within an existing macro that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder

nWB = Workbooks.Count

HTH
--
AP

"Hervinder" a écrit dans le message de
news: ...

I am trying to write a simple code within an existing macro that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder

Use Workbooks.Count, but be aware it will count Personal.xls if you have
one, and any other files in XLStart that get opened automatically.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Hervinder" wrote in message
...

I am trying to write a simple code within an existing macro that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder

Dim wkbBook as Workbook
Dim lCount as long
For each wkbBook in Application.Workbooks
if wkbBook.Windows(1).visible Then lCount = lCount + 1
next wkbBoo

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=53839

Try the following function:


Function WorkbookCount(Optional VisibleOnly As Boolean = False)
Dim WB As Workbook
If VisibleOnly = True Then
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
WorkbookCount = WorkbookCount + 1
End If
Next WB
Else
WorkbookCount = Workbooks.Count
End If
End Function


You can then use it in code like

If WorkbookCount(VisibleOnly:=False) 1 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Hervinder" wrote in
message
...

I am trying to write a simple code within an existing macro
that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder