Check if form is open

[Guest]

Can someone help me out with this one?
I am trying to determine if an Excel form is open or not from within Excel.
Thanks in advance.

 

[Sharad]

I assume you mean a UserForm
If you want to know if an userform is actually shown or not then
you can use

' If uf1.Visible Then '

Sharad

 

[Tom Ogilvy]

Use the FindWindow API

Using the FindWindow API call to get the Windows handle for an Excel
UserForm:


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long


Then in your code do:


hWnd = FindWindow ("ThunderDFrame", Me.Caption)
If hWnd = 0 then

msgbox "Not found"

End if



Replace Me.Caption with the caption of the Userform you are searching for.

I believe in Excel 97, the class name is ThunderXFrame

 

[Tom Ogilvy]

Just for information,
note that doing

if uf1.visible

implicitely loads uf1 if it wasn't already loaded.

 

[Tom Ogilvy]

I had the D and the X backwards - X is for xl2000 and later and D is for
xl97. From a post by Chip Pearson:

William,


You can use the FindWindow API to get the hWnd of the form. The class name
is
"ThunderXFrame" in VBA6 (Excel 2000 and 2002) or "ThunderDFrame" in VBA5
(Excel97). E.g.,


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hWND As Long, ByVal wMsg As Long, ByVal wParam _

As Long, lParam AsAny) As Long
Public Const WM_CLOSE = &H10


Sub AAA()
Dim hWND As Long
UserForm1.Show vbModeless
#If VBA6 Then
hWND = FindWindow("ThunderDFrame", UserForm1.Caption)
#Else
hWND = FindWindow("ThunderXFrame", UserForm1.Caption)
#End If


SendMessage hWND, WM_CLOSE, 0, 0
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)


--

Regards,

Tom Ogilvy

 

[Alex J]

How about:
For each uf in Userforms
msgbox uf.Name
Unload uf
next

("Userforms" is the collection of loaded userforms.)
Alex J

 

[Tom Ogilvy]

Sub AA11()
Load UserForm1
For Each uf In UserForms
MsgBox uf.Name
Unload uf
Next

End Sub


The message box shows userform1 is loaded, but it isn't shown - guess it
depends on what is meant by open.

 

[Tom Ogilvy]

And after some testing, my method finds it whether it is visible or not as
well.

so with your message you would probably want to add

For Each uf In UserForms
MsgBox uf.Name & " " & uf.Visible
Unload uf
Next

 

[Alex J]

OK Tom,
But since you are only checking the collection of loaded forms, adding:
msgbox uf.visible
allows you to verify loaded or visible status, but doesn't load userfoms
unintentionally.
Alex J

 

[Tom Ogilvy]

agreed - see my previous post.