Solved: Calling a user form

[imellor]

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

[Bob Phillips]

[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]

[imellor]

[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

[Paul_Hossler]

Assuming that sForm exists already ...

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

should work

Paul

[imellor]

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

[Aussiebear]

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

[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]

[Bob Phillips]

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.

[mikerickson]

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.

[Paul_Hossler]

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.

[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

[Kenneth Hobs]

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

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

[Bob Phillips]

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.

[imellor]

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

[imellor]

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