Check if userform is loaded in another workbook

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Check if userform is loaded in another workbook

Post by YasserKhalil »

Hello everyone
I have a code that detects if a userform is loaded or not (The code works in the workbook that has the code)

Code: Select all

Sub Test_IsLoaded()
    If IsLoaded("UserForm1") Then
        MsgBox "UserForm Is Open", 64
    End If
End Sub

Function IsLoaded(formName As String) As Boolean
    Dim frm As Object
    For Each frm In VBA.UserForms
        If frm.Name = formName Then
            IsLoaded = True
            Exit Function
        End If
    Next frm
    IsLoaded = False
End Function
How can I detect a userform that is loaded but this userform is in another opened workbook?

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Check if userform is loaded in another workbook

Post by Doc.AElstein »

I think this works for me

Code: Select all

'     formName is code name           WkBkNme is workbook name
Function IsLoaded(formName As String, WkBkNme As String) As Boolean
Dim VbComp As Object
    For Each VbComp In Workbooks("" & WkBkNme & "").VBProject.VBComponents
        If VbComp.Name = formName Then
         Let IsLoaded = True
         Exit Function
        End If
    Next VbComp
 Let IsLoaded = False
End Function
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Check if userform is loaded in another workbook

Post by YasserKhalil »

Thanks a lot. I have tested the function and when the userform is not open, I got a message it is open!!

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check if userform is loaded in another workbook

Post by HansV »

Alan's code only checks whether the userform exists in that workbook, not whether it is loaded.

As far as I know, Excel VBA does not offer a built-in way to check this. It might be possible using Windows API functions, but that would be complicated and I cannot help you with that.

Is the other workbook developed by you, i.e. could you edit its code?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Check if userform is loaded in another workbook

Post by YasserKhalil »

Yes I can edit both workbooks.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check if userform is loaded in another workbook

Post by HansV »

You could store a value in a cell in the workbook when you open the userform, and clear that cell when you close the userform.
The cell can be in a hidden worksheet if desired.

In the workbook from which you want to perform the check, you can inspect the cell in the other workbook. If it is not blank, the userform is loaded.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Check if userform is loaded in another workbook

Post by YasserKhalil »

That's a great idea my tutor. I have put the following codes in the userform in the other workbook

Code: Select all

Private Sub UserForm_Initialize()
    ThisWorkbook.Worksheets("Sheet2").Range("K1").Value = "UserForm Loaded"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ThisWorkbook.Worksheets("Sheet2").Range("K1").Value = Empty
End Sub
In the workbook named 1.xlsm I tried that code

Code: Select all

Sub CheckUserFormInAnotherWOrkbook()
    Dim wb As Workbook
    Set wb = Workbooks("2.xlsm")
    wb.Activate
    If wb.Worksheets("Sheet2").Range("K1").Value <> Empty Then
        MsgBox "UserForm In The Workbook Named '1.xlsm' Is Open", 64
    End If
End Sub
Thank you very much.
Last question, how can I open the userform from the workbook named 1.xlsm?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check if userform is loaded in another workbook

Post by HansV »

You need to have a macro in the workbook with the userform to open it. For example:

Code: Select all

Sub OpenUserForm()
    UserForm1.Show
End Sub
(using the actual name of the userform)

Then in the other workbook, call the macro like this:

Code: Select all

Sub OpenUserformInOtherWorkbook()
    Application.Run "'Book1.xlsm'!OpenUserForm"
End Sub
(using the actual name of the workbook of course)
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Check if userform is loaded in another workbook

Post by YasserKhalil »

Thank you very much my tutor.
Best and Kind Regards