Excel97 VBA: How can I count open workbooks that are not hidden?

Question

Excel97 VBA: How can I count open workbooks that are not hidden?

BarryG (Programmer)

(OP)

23 Feb 00 12:47

In a function I'm writing, I need to find the number of open workbooks, but I want to exclude those that are hidden. Please help!

Thanks
Barry

Mon (ISP) · Answer 1 · 2000-02-24 13:22:13

Hi Barryg

As you probably know there is no method to count visible workbooks. However, try this routine and I hope it works

Sub VisibleWorkbooks()
'Count the number of Open Workbooks
vopenworkbooks = Application.Windows.Count
vvisible = 0
'Loop to check is the workbook hidden
For counter = 1 To vopenworkbooks
If Windows(counter).Visible = True Then vvisible = vvisible + 1
Next
MsgBox ("the Number of Workbooks open and not hidden is " & vvisible)

End Sub

Let me know how you get on!!

Regards
Monica

BarryG (Programmer) · Answer 2 · 2000-02-24 14:45:36

Thanks, Monica. It worked well. I was stuck because visible wasn't a valid property of the workbooks collection and I didn't know about the Windows collection. I'm very new to VBA in Excel so it's taking a while to learn to collection, properties, methods, etc.

Thanks again,
Barry

Come Join Us!

Posting Guidelines

Jobs

Excel97 VBA: How can I count open workbooks that are not hidden?