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.:banghead:
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 (http://www.cpearson.com/Excel/showanyform.htm) for a better example.

Hope that helps?

Mark

[keebellah]

Hey guys Thank you :thumb:thumb:thumb
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:)