Checking to see how many workbooks are visible

[Andrew]

I've written a macro I need to incorporate into a spreadsheet to send out to
other users in the business. At the moment the only time it seems to crash is
if there are no workbooks visible / active when it is set running (which
could happen).

How do I check how many workbooks are currently visible before the rest of
the macro starts running?

Thanks,

Andrew

 

[Rowan]

One way:

Sub bks()
Dim i As Integer
Dim bk As Workbook
For Each bk In Workbooks
If bk.Name <> "PERSONAL.XLS" Then
i = i + 1
End If
Next bk
MsgBox "Open Books: " & i
End Sub

Or:

Sub winds()
Dim i As Integer
Dim wnd As Window
For Each wnd In Windows
If wnd.Visible = True Then
i = i + 1
End If
Next wnd
MsgBox "Visible Windows: " & i
End Sub

Hope this helps
Rowan

 

[Andrew]

The second one does the trick.

Thanks,

Andrew

One way:

Sub bks()
Dim i As Integer
Dim bk As Workbook
For Each bk In Workbooks
If bk.Name <> "PERSONAL.XLS" Then
i = i + 1
End If
Next bk
MsgBox "Open Books: " & i
End Sub

Or:

Sub winds()
Dim i As Integer
Dim wnd As Window
For Each wnd In Windows
If wnd.Visible = True Then
i = i + 1
End If
Next wnd
MsgBox "Visible Windows: " & i
End Sub

Hope this helps
Rowan