PDA

View Full Version : VBA: Create a List of Userforms and make check visible one's



RicardoBarao
11-23-2018, 12:49 PM
Situation:
I have a useform which is dependent of other visible Userforms, so I want to make a list with the UserForms that my project has and then I want to check which of them are visible or not.

I think it should be something like this:

Sub Test()

Dim uform As UserForm

For Each uform In VBA.UserForms


If uform.Visible Then
...
exit sub

End If


Next


End Sub

However when I run this SUB it says i dont have any UserForms, it says the loop is 0.

Thank you for your help.

Dave
11-23-2018, 01:22 PM
Maybe something like this...

Dim c As Object
For Each c In ThisWorkbook.VBProject.VBComponents
If c.Visible Then
MsgBox c.Name & " is visible"
Else
Msgbox c.Name & " is NOT visible"
End If
Next c
HTH. Dave

Paul_Hossler
11-23-2018, 01:33 PM
You have to loop the userforms a little different

But, I don't think that there's a .Visible property so you will probably need to maintain Visible status yourself. The sample uses a collection for that




Option Explicit

Sub Test()
Dim collUserforms As Collection
Dim oUserForm As Object
Set collUserforms = New Collection

'init
For Each oUserForm In ThisWorkbook.VBProject.VBComponents
If oUserForm.Type = 3 Then
collUserforms.Add False, oUserForm.Name
End If
Next

Load UserForm1
UserForm1.Show False

collUserforms.Remove "UserForm1"
collUserforms.Add True, "UserForm1"

For Each oUserForm In ThisWorkbook.VBProject.VBComponents
If oUserForm.Type = 3 Then
If collUserforms(oUserForm.Name) Then
MsgBox "Visible"
Else
MsgBox "Not Visible"
End If
End If
Next
End Sub

rlv
11-23-2018, 09:00 PM
I think it should be something like this:


Sub Test()

Dim uform As UserForm

For Each uform In VBA.UserForms
If uform.Visible Then
...
exit sub

End If
Next uform
End Sub

However when I run this SUB it says i dont have any UserForms, it says the loop is 0.

Thank you for your help.

There's nothing wrong with that approach, but it is only going to show you UserForms that have been loaded. Also you have to define uform as an object

For example, this looks to be sure that Userform2 is loaded and visible:


Sub Test()
Dim uform As Object

For Each uform In VBA.UserForms
If uform.Name = "UserForm2" And uform.Visible Then
Exit Sub
End If
Next uform
MsgBox "Warning - cannot find UserForm2!"
End Sub

snb
11-24-2018, 04:33 AM
You'd better use 1 Userform with 1 multipage in it instead of several userforms.

Leith Ross
11-24-2018, 04:37 PM
Hello RicardoBarao,

The UserForm does not has a Visible property. Since it is a window, all windows have a visible state flag. This can be used to determine if the UserForm is loaded or hidden.

Only one window may ever have the focus (be active). This macro will tell you if one of the UserForms is the Active Window (has focus) along with it's window handle, show state, and if it is visible. Add a new VBA Module to your workbook. Copy the code below and paste it into the new module.

I have a attached a sample workbook to demonstrate the macro.



' Written: November 24, 2018
' Author: Leith Ross
' Summary: Returns the window handle, the window state, visibilty, and if a VBA UserForm has the focus.
' The UserForm must be loaded or shown non-modally for the macro to run.




Type POINTAPI
x As Long
y As Long
End Type


Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type


Type WINDOWPLACEMENT
Length As Long
flags As Long
showCmd As Long
ptMinPosition As POINTAPI
ptMaxPosition As POINTAPI
rcNormalPosition As RECT
End Type


Private Declare PtrSafe Function GetForegroundWindow Lib "User32.dll" () As LongPtr
Private Declare PtrSafe Function IsWindowVisible Lib "User32.dll" (ByVal hWnd As LongPtr) As Long
Private Declare PtrSafe Function GetWindowPlacement Lib "User32.dll" (ByVal hWnd As LongPtr, lpwndpl As WINDOWPLACEMENT) As Long
Private Declare PtrSafe Function WindowFromAccessibleObject Lib "Oleacc.dll" (ByVal pacc As IAccessible, ByRef phwnd As LongPtr) As Long




Sub ShowUserFormStatus()


Dim hWnd As LongPtr
Dim n As Long
Dim ret As Long
Dim state As Long
Dim uf As Object
Dim wnd As LongPtr
Dim wp As WINDOWPLACEMENT

wp.Length = Len(wp)

wnd = GetForegroundWindow()

For n = 0 To UserForms.Count - 1
Set uf = UserForms(n)

ret = WindowFromAccessibleObject(uf, hWnd)

If ret = 0 Then
ret = GetWindowPlacement(hWnd, wp)
If ret <> 0 Then
MsgBox uf.Name & vbLf _
& vbTab & "Window Handle = " & hWnd & vbLf _
& vbTab & "Window State = " & wp.showCmd & vbLf _
& vbTab & "Visible = " & (IsWindowVisible(hWnd) <> 0) & vbLf _
& vbTab & "Has Focus = " & (hWnd = wnd)
End If
End If
Next n

End Sub




Sub TestIt()


' // The last UserForm shown will have the focus.
UserForm2.Show False
UserForm1.Show False
Load UserForm3

ShowUserFormStatus

End Sub

mikerickson
11-25-2018, 02:21 PM
You could use code like this.
One complication is that the Userforms collection holds only instances of userforms that are loaded.
Another is that code that references a userform before it is shown (e.g. MsgBox UserForm1.Visible) will load the userform if it hasn't already been loaded.


Sub UFSummary()
Dim arrAllUFNames() As String
Dim arrUFLoaded() As Boolean
Dim arrUFVisible() As Boolean
Dim oneComp As Object, testUF As Object
Dim strDisplay As String
Dim ufCount As Long, i As Long

With ThisWorkbook.VBProject.VBComponents
ReDim arrAllUFNames(1 To .Count)
End With

ufCount = 0
For Each oneComp In ThisWorkbook.VBProject.VBComponents
With oneComp
If .Type = 3 Then
ufCount = ufCount + 1
arrAllUFNames(ufCount) = .Name
End If
End With
Next oneComp

If 0 < ufCount Then
ReDim Preserve arrAllUFNames(1 To ufCount)
ReDim arrUFLoaded(1 To ufCount)
ReDim arrUFVisible(1 To ufCount)
For i = 1 To ufCount
Set testUF = UserFormNamed(arrAllUFNames(i))
If Not testUF Is Nothing Then
arrUFLoaded(i) = True
arrUFVisible(i) = testUF.Visible
End If
Next i

For i = 1 To ufCount
strDisplay = arrAllUFNames(i) & " is "
strDisplay = strDisplay & IIf(arrUFLoaded(i), "", "not ") & "loaded, is "
strDisplay = strDisplay & IIf(arrUFVisible(i), "", "not ") & "visible."
If MsgBox(strDisplay, vbOKCancel) = vbCancel Then Exit Sub
Next i
Else
MsgBox "This project has no userforms, loaded or otherwise"
End If

End Sub

Function UserFormNamed(ufName As String) As Object
Dim oneForm As Object
For Each oneForm In UserForms
If oneForm.Name = ufName Then
Set UserFormNamed = oneForm
Exit For
End If
Next oneForm
End Function

Note that this code does not address a situation where more than one instance of the same userform is loaded at the same time.