| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if form is open
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
Just for information,
note that doing if uf1.visible implicitely loads uf1 if it wasn't already loaded. -- Regards, Tom Ogilvy "Sharad" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
How about:
For each uf in Userforms msgbox uf.Name Unload uf next ("Userforms" is the collection of loaded userforms.) Alex J "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
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. -- Regards, Tom Ogilvy "Alex J" wrote in message ... How about: For each uf in Userforms msgbox uf.Name Unload uf next ("Userforms" is the collection of loaded userforms.) Alex J "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
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 -- Regards, Tom Ogilvy "Alex J" wrote in message ... How about: For each uf in Userforms msgbox uf.Name Unload uf next ("Userforms" is the collection of loaded userforms.) Alex J "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Alex J" wrote in message ... How about: For each uf in Userforms msgbox uf.Name Unload uf next ("Userforms" is the collection of loaded userforms.) Alex J "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if form is open
agreed - see my previous post.
-- Regards, Tom Ogilvy "Alex J" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Alex J" wrote in message ... How about: For each uf in Userforms msgbox uf.Name Unload uf next ("Userforms" is the collection of loaded userforms.) Alex J "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Kev" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| make a check form, and then have info go to a check register | Excel Worksheet Functions | |||
| Check Box and form | Excel Discussion (Misc queries) | |||
| Using a template form, advance a form number everytime you open | Excel Discussion (Misc queries) | |||
| form check box | Excel Worksheet Functions | |||
| Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form? | Excel Programming | |||
Linear Mode
