CODEWITHSUNDEEP
excelvba
Rahul Khosla
Rahul Khosla

Reputation: 190

How to check if ANY excel workbook is open?

I have a VBA project in excel which hides the workbook in the background and only shows the userform, it works fine however, causes problems when other workbooks are open as it hides them also. To prevent this I am trying to find some method of placing an if statement in the code which checks if any excel workbook is open at the time of start-up. Is this possible? All I can find online is how to check this if the open file name is known.

Upvotes: 2

Views: 5533

Answers (3)

jreichert
jreichert

Reputation: 1566

I think, since 'ActiveWorkbook' can also be empty, the safest answer for VBA may be:

Option Explicit

Public Function IsAnyWorkbookOpen() As Boolean
    IsAnyWorkbookOpen = (Application.Workbooks.Count > 0)
End Function

Sub test()
    MsgBox ("IsAnyWorkbookOpen returns: " + CStr(IsAnyWorkbookOpen()))
End Sub

Upvotes: 3

Kevin Francis
Kevin Francis

Reputation: 385

The ideal solution is not to force a multitasking Excel user to close all their workbooks in order to use the one with the user form, but to specifically address the workbook in your code so that it doesn't effect other workbooks. You can use ActiveWorkbook.FullName for this.

Upvotes: 0

Barranka
Barranka

Reputation: 21047

This may help you:

Public Sub openWorkbooks()
    Dim wb As Excel.Workbook
    For Each wb In Excel.Workbooks
        ' Do whatever you need to do here
        ' This prints the workbook name to the inmediate window
        Debug.Print wb.Name
    Next wb
End Sub

Upvotes: 1

Related Questions