Userforms.show versus getting UserForms as Object looping through the VBA.UserForms.Collection

Question

I want to select userforms safely without accidentally loading new instances of the userforms, at the same time I want to avoid too much code. So far I have addressed userforms directly, i.e. with the caption of the userform, e.g.

Userform1.Show

But often I need or want to search and load userforms by name in the VBA.project, because it would simplify some functions for me. But since I use both variants, depending on how the code is executed, a second instant is created. I want to avoid this. I can convert any direct call (Userform1.Show) to Show_Userform("Userform1"), but there are numerous other functions that use Userform1 directly and I would have to convert all of them to objects first to continue abriting.

The real question I have is is there a safe way when calling UserForm1.Show to load the already loaded instance Show_Userform("UserForm1") instead of creating a new one? I read through Difference between declaring a userform as Object vs MSForms.Userform? which was helpful but my problem is not described

Sub Test_Works()
Dim obj As Object
    
    UserForm1.Show (False)
    Show_Userform ("UserForm1")

End Sub

Sub Test_Creats_Second_Instance()
Dim obj As Object
    
    'Is there a way to avoid that second instance being created?
    Show_Userform ("UserForm1")
    UserForm1.Show (False)

End Sub

Sub Show_Userform(sName As String)

Dim obj As Object
    
    '~~> Set userform if still loaded
    For Each obj In VBA.UserForms
        If StrComp(obj.Name, sName, vbTextCompare) = 0 Then
            obj.Show (False)
            Exit Sub
        End If
    Next obj
    
    '~~> Get userform if not loaded yet
    On Error Resume Next
        Set obj = VBA.UserForms.Add(sName) 'triggers Initialize of the Userform
            obj.Show (False)
    On Error GoTo 0
    
End Sub

`is there a safe way when calling UserForm1.Show to load the already loaded instance Show_Userform("UserForm1")` - no. The automatically created instances are separate from the manually created instances. Use one or another. — GSerg, Jul 29 '23 at 16:04
If you want to ensure you are working with the same instance of a UserForm then the recommendation would normally be to *not* use the default instance (ie don't use `UserForm1.Show`) and instead create a specific instance eg `Dim uf1 as UserForm1` then `Set uf1 = New UserForm1` then `uf1.Show` ... you can then use `uf1` (and pass it to other procedures) to manage that one instance, show it, hide it, show it again and, eventually unload it. However this seems to be the opposite of what you want? Perhaps you can explain *why* you want to use the default instance (if I've understood correctly)? — JohnM, Jul 29 '23 at 16:14
A workaround is to pre-load all automatically created instances at the start of your program (i.e. by reading a non-consequential property). That way your `Show_Userform` will always find an existing instance (which will always be the auto-created one) and will never add any. — GSerg, Jul 29 '23 at 16:20
Hello John, you have understood me correctly. I have been using so many standard userforms that now it is hard to switch. I didn't know until recently that it makes a difference to assign the userforms via the VBA.project. I originally wanted to save code in many places where the same thing is done over and over again only with different userforms, in which I outsource a function, which then assigns the userform by string instead of with the defaultform. But in that case the conversion would have meant in all places in the code that I do not save code, but have significantly more than before.. — crusader86x, Jul 29 '23 at 23:29
Hi Greg, thanks for the tip, that sounds like a good solution. I would then initialize all userforms in Workbook_Open if necessary. :-) Another solution would be to initialize the direct userform in Show_Userform or a function that only initializes the userforms via select case string for each string, if it is not yet in the VBA.project. — crusader86x, Jul 29 '23 at 23:29
You might benefit from studying [UserForm1.Show](https://rubberduckvba.blog/2017/10/25/userform1-show/), an article by Mathieu Guindon. The same author has [many answers](https://stackoverflow.com/search?tab=votes&q=user%3a1188513%20userform&searchOn=3) related to user forms here on SO. — VBasic2008, Jul 30 '23 at 18:41

score 0 · Answer 1 · answered Jul 30 '23 at 08:54

With Greg's tip I found a good solution to my problem:

Sub Test_Solution()

Dim obj As Object

   'creates no second instance
   Set obj = Get_Userform("UserForm1")
   obj.Show (False)
   UserForm1.Show (False)

End Sub

Function Get_Userform(sName As String) As Object

Dim obj As Object

    '~~> Set userform if still loaded
    For Each obj In VBA.UserForms
        If StrComp(obj.Name, sName, vbTextCompare) = 0 Then
            Set Get_Userform = obj
            Exit Function
        End If
    Next obj

    '~~> Get userform if not loaded yet
    Select Case sName
        Case "UserForm1": Set Get_Userform = UserForm1
        Case "UserForm2": Set Get_Userform = UserForm2
        Case Else: Err.Raise vbObjectError, , "Error in Get_Userform: sName not defined"
    End Select

End Function

Userforms.show versus getting UserForms as Object looping through the VBA.UserForms.Collection

1 Answers1