Solved: Calling a user form

I am a newbie to VBA and I am currently trying to call a userform a command button.

I am trying to write the code, so it can be reused hence using the string sForm

' When "Update button on "Dashboard" clicked, open user form
[VBA]Private Sub Update1_Click()
Call OpenUserForm("UsrFrmGetFile")
End Sub[/VBA]

and

[VBA]Option Explicit
Sub OpenUserForm(sForm As String)
sForm.Show
End Sub[/VBA]


However I recieve a compile error saying "invalid qualifier", I have searched this forum and Google, but can't understand how to pass UsrFrmGetFile to my OpenUserForm function.

Can anyone point me in the correct direction please?

Ian

[VBA]' When "Update button on "Dashboard" clicked, open user form
Private Sub Update1_Click()
Call OpenUserForm("UsrFrmGetFile")
End Sub

Public Sub OpenUserForm(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = UserForms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & " was not found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, "Load userforn by name"
End Select
End Sub[/VBA]

Quote:

---

Originally Posted by xld

[VBA]' When "Update button on "Dashboard" clicked, open user form
Private Sub Update1_Click()
Call OpenUserForm("UsrFrmGetFile")
End Sub

Public Sub OpenUserForm(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = UserForms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & " was not found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, "Load userforn by name"
End Select
End Sub[/VBA]

---

Thank you for your prompt response, am I right in thinking that my code didn't work, because the form is an object?

Also where do you look up UserForms.add, I looked here, but is not listed msdn.microsoft.com/en-us/library/bb149081.aspx

Ian

Assuming that sForm exists already ...

[VBA]
Option Explicit
Sub OpenUserForm(sForm As String)
UserForms (sForm).Show
End Sub
[/VBA]

should work

Paul

Quote:

---

Originally Posted by Paul_Hossler

Assuming that sForm exists already ...

[vba]
Option Explicit
Sub OpenUserForm(sForm As String)
UserForms (sForm).Show
End Sub
[/vba]

should work

Paul

---

Paul thanks for your suggestion, unfortunately I received a type mismatch error.

Ian

try the following;
[VBA]
Option Explicit
Sub OpenUserForm(sForm As String)
Userform (SForm).Show
End Sub
[/VBA]

Did you need to pass a string to open the userform? Have you considered just doing it in the commandbutton?

[VBA]Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
[/VBA]

Quote:

---

Originally Posted by imellor

Thank you for your prompt response, am I right in thinking that my code didn't work, because the form is an object?

Also where do you look up UserForms.add, I looked here, but is not listed msdn.microsoft.com/en-us/library/bb149081.aspx

Ian

---

Trying to show a string would never work for sure, but as you saw with Paul's attempt, trying to reference an object with that name does not work either as the Userforms collection consists of all forms in that project in memory, not all forms in that project. Until a form is shown, it does not exist in the Userforms collection; ergo, you cannot access it as a named instance in that collection.

My code works because the first time you reference a form, it is added to the Userforms collection. I can add by name, setting that instance to an object variable, and then show that object.

Excel 2011 for Mac does not support the .Add method of the useforms collection. (I can't see a way to show a userform given a string.)

Oddly enough, the .Add method was supported in Excel 2004.

Quote:

---

but as you saw with Paul's attempt, trying to reference an object with that name does not work either as the Userforms collection consists of all forms in that project in memory, not all forms in that project. Until a form is shown, it does not exist in the Userforms collection; ergo, you cannot access it as a named instance in that collection.

---

I think the last time XLD made a mistake was in 1995.

He's right, so I learned something today also. I always thought just having it in the Project was enough

I also found out that for some reason that even if it is Loaded, you can't refer to it by name, but by index. That seems a little odd, but this is MS and Excel and VBA.:think:

[vba]
Option Explicit
Sub Update1_Click()
Load UsrFrmGetFile
Call OpenUserForm("UsrFrmGetFile")
Call OpenUserForm_1("UsrFrmGetFile")
End Sub

Sub OpenUserForm(sForm As String)
Dim i As Long
For i = 0 To UserForms.Count - 1
If UserForms(i).Name = sForm Then UserForms(i).Show
Next
End Sub

'Doesn't work
Sub OpenUserForm_1(sForm As String)
UserForms(sForm).Show
End Sub

[/vba]


Paul

Chip Pearson's routine uses the Add concept that xld used.

http://www.cpearson.com/excel/ShowAnyForm.htm

Quote:

---

Originally Posted by Paul_Hossler

I think the last time XLD made a mistake was in 1995.

---

I wish :). Making mistakes is the only way I can gauge I am still here :( .

This one is buried on my consciousness because I had a problem some years ago, and I had a conversation with the great Stephen Bullen about it where we (mainly he) worked it all out. I cannot remember the exact details.

Quote:

---

Originally Posted by Kenneth Hobs

Did you need to pass a string to open the userform? Have you considered just doing it in the commandbutton?

[VBA]Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
[/VBA]

---

Thank you, being a newbie, I was using the wrong Syntax. That works fine

Ian

Quote:

---

Originally Posted by xld

Trying to show a string would never work for sure, but as you saw with Paul's attempt, trying to reference an object with that name does not work either as the Userforms collection consists of all forms in that project in memory, not all forms in that project. Until a form is shown, it does not exist in the Userforms collection; ergo, you cannot access it as a named instance in that collection.

My code works because the first time you reference a form, it is added to the Userforms collection. I can add by name, setting that instance to an object variable, and then show that object.

---

Thanks for the explanation.

Ian