Excel VBA refer to a userform by variable to address it

[keebellah]

Hi Guys,
I have been searching for a solution what in my opinion should be quite simple.
I have a named range on a worksheet that contains the names of several userforms.

what I cannot seem to get working is to use the name in this range to address one of these forms
part of the code:

Dim uRng As Range
Dim uForm as Userform  ' (I tried Object by no go)
For Each uRng In Worksheets("Sheet3").Range("USERFORMS")
     set Uform = Userforms(uRng)
     debug. print Uform.Caption
    Set uForm = Nothing
Next uRng

The Userform names are valid an exist if I use set Uform = Userform1 it works
Any ideas? Suggestions?

Thanks for looking

[keebellah]

Additional Information
I have CommandButtons and the name of the userform is the Caption so that when I press that button
I want to use the Caption as variable to Show that Userform

Private Sub CommandButton1_Click()
UserForms(Me.CommandButton1.Caption).Show
End Sub

Hope it makes sense

[SamT]

You have to Load a UserForm before it it will be in UserForms. IOW, UserForms only contains Loaded Forms. Load all UserForms on Workbook Open.

OR: Create Public Custom Collection, "MyUserForms" and Fill it on Workbook Open. Then

Private Sub CommandButton1_Click() 
    MyUserForms(Me.CommandButton1.Caption).Show 
End Sub

IMO, simpler

Private Sub CommandButton1_Click() 
    ShowForms Me.CommandButton1.Caption
End Sub 

Private Sub CommandButton2_Click() 
    ShowForms Me.CommandButton1.Caption
End Sub 
'Etc

Private Function ShowForms(NameForm as String)

If NameForm = "UserForm1" then UserForm1.Show
If NameForm = "UserForm2" then UserForm2.Show
'Etc

EndFunction

IMO, Best:

Private Sub cbutDataEntry_Click() 
'Caption = "Enter New Data"
    frmDataEntry.Show 
End Sub

I am a proponent of having all related Objects having related Names that indicate their purpose in life.

For Example:

• SheetObject.Name = "dbPersonnel". (Sheet CodeName)
• Sheet Tab name = "Personnel"
• Named Range "rngFirstName", RefersTo Column w/Header = "First Name"
• CommandButton Name = "cbutShowfrmPersonnel", Caption = "Enter New Employee"
• UserForm.Name = frmPersonnel, Caption = "Personnel Data Entry Form"
• Label1.Text="First Name"
• TextBox.Name= txtFirstName, Value goes in dbPersonnel.Range("rngFirstName")

[keebellah]

Hi Sam, Thanks for the answer and yes the way you explain it is known and that already works but what I am looking for is different.
My first explanation was (reading your answer) not correct for which I excuse myself.
The idea is as follows:
I have one Userform which has 10 CommandButtons all with their standard namse CommandButton1, 2, etc 10 (code below)

Option Explicit
Const btnHeight As Long = 42

Private Sub userform_initialize()
Dim xLoop   As Integer
Dim uRng    As Range
Dim frmH    As Long
Dim uForm   As Object

For xLoop = 1 To 10
    Me.Controls("CommandButton" & xLoop).Visible = False
Next xLoop
xLoop = 0
For Each uRng In Worksheets("Sheet3").Range("USERFORMS")   ' This named range contains a dynamic named list of 1 to 10 userform names  UserForm1, 2, or other etc and the Offset(0,1) is the actual Caption text to show on the button
    xLoop = xLoop + 1
    With Me.Controls("CommandButton" & xLoop)
        .Visible = True
        .Caption = uRng.Offset(0, 1)
        .Font.Bold = True
        .Tag = "show" & uRng   ' the complete text here results "showUserForm1" where 1 goes from 1 to 10 and a macro has to be present in the VBA Project Public Sub showUserForm1()
        .Top = 10 + IIf(xLoop = 1, 0, (xLoop - 1) * 52)
    End With
    frmH = xLoop * 52
    Set uForm = Nothing
    If xLoop = 10 Then Exit For
Next uRng
frmH = frmH + 35
Me.Caption = "Make your choice"
Me.Height = frmH
End Sub

Private Sub UserForm_Activate()
Me.Left = 50 '(Application.Width / 2) - (Me.Width / 2)
Me.Top = (Application.Height / 2) - (Me.Height / 2) 'btnHeight
Me.BtnEXIT.SetFocus
End Sub

Private Sub BtnEXIT_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' This is to force the user to use the Cancel button
    If CloseMode = vbFormControlMenu Then
        Cancel = True
    End If
    BtnEXIT.SetFocus
End Sub

Private Sub CommandButton1_Click()
Unload Me
Application.Run (Me.CommandButton1.Tag)
End Sub

Private Sub CommandButton2_Click()
Unload Me
Application.Run (Me.CommandButton2.Tag)
End Sub

' and the rest

Private Sub CommandButton10_Click()
Unload Me
Application.Run (Me.CommandButton2.Tag)
End Sub

All the 10 commandbuttons have a CommandButton?_Click() that will then unload this form and run the macro opening the form

What I am looking for is to place just place the userform name in the .Tag and when the CommanButton?_Click is clicked
just use the Me.CommandButton9.Tag without having to create an extra macro for this

The idea:

Private Sub CommandButton9_Click()
Dim uForm  As Userform
Unload Me
set Uform = Me.CommandButton9.Tag
uForm.show
End Sub

I hope this explains my idea.

[keebellah]

I compiled a sample file explaining my question
The CommandButtons on the Userform are all on the same spot so you only see one but in fact there are 10

And NO I do not want to access the VBA Project programmatically because it will be protected and hidden

[GTO]

Greetings Hans,

As far as I can tell, you would only be gaining the reduction of this code:

Public Sub showUserForm4()
Worksheets("Database").Activate
UserForm4.Show
showFormSelection
End Sub

and...if you want access to the chosen form's controls after that, you might be looking at CallByName.

Anyways, in a Junk copy of your workbook, just for quick observations, add a Standard Module and add this code:

Option Explicit
  
Sub example()
Dim objForm As Object
  
  Set objForm = UserForms.Add("UserForm3")
  objForm.Show
  
End Sub
  
Sub HansExample()
Dim uRng As Range
Dim uForm As Object '<--- You want Object, not As UserForm, which will not allow access to the object's properties
      

    For Each uRng In Worksheets("Sheet3").Range("USERFORMS")
        Set uForm = UserForms.Add(uRng)
        Debug.Print uForm.Caption & " count = " & UserForms.Count
        Set uForm = Nothing
    Next uRng
    
    
    For Each uForm In UserForms
      Debug.Print uForm.Name
      Unload uForm
      Debug.Print UserForms.Count
      Debug.Print AreUserFormsLoaded
    Next
    
End Sub
  
Function AreUserFormsLoaded() As Boolean
' Function to detect if a userform is loaded in memory, this can be used to avoid
' unexpected actions if procedures were executed and a userform is loaded
Dim UFormchk As Object
  For Each UFormchk In VBA.UserForms
      AreUserFormsLoaded = True
      Exit Function
  Next
End Function

To Userforms 1 - 4, add this code:

Option Explicit
  
Private Sub UserForm_Initialize()
  Stop
End Sub
  
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Stop
End Sub
  
Private Sub UserForm_Terminate()
  Stop
End Sub

If you step thru (press F8) 'example()', you will see that the form is instantiated at UserForms.Add("UserForm3") and of course displayed at .Show.

With the Immediate Window showing, step through HansExample(). I would mention, and this is at Sam's point, that testing for Is Nothing for each member in UserForms (as AreUserFormsLoaded shows in your workbook) is redundant. If the member exists, then it is not Nothing. Does that make sense?

As to the remainder, note that in the first loop, the count keeps climbing even though you have set uForm to Nothing each time thru the loop. This is because you are setting the variable uForm to reference Nothing, but the loaded Object remains in memory. You would normally want to unload the form before setting the reference to the form to Nothing.

The second loop of course just gets rid of the forms in memory and shows the shortened code in AreUserFormsLoaded().

Look at Show any Form by Chip Pearson for a better example.

Hope that helps?

Mark

[keebellah]

Hey guys Thank you
Greetings GTO, you did it. I was messing up with the Object and Range part.
The actual file works too with all the 'real' userforms.
I have attached the updated working Sample including Error trapping just in case the userform's name has been accidentally misspelled or does not exist.
Thanks again, I just couldn't put my finger on it

[GTO]

You are most welcome. FWIW, I would only use the On Error Resume Next for the Set statement and then test (regardless of whether I included the Else and MsgBox). Again, just FWIW.

Public Sub showUserForm(myForm As String)
Dim objForm As Object


  Worksheets("Database").Activate
  
  On Error Resume Next
  Set objForm = UserForms.Add(myForm)
  On Error GoTo 0
  
  If Not objForm Is Nothing Then
    objForm.Show
  Else
    MsgBox "Error"
  End If
  
End Sub

Mark

[keebellah]

That's the way too, but I also have one in the userform with the buttons
Thanks again, you sometimes get stuck in the known and frequently used routines that you miss the seeing the obvious.

Never too old to learn