PDA

View Full Version : Solved: Calling a user form



imellor
07-04-2012, 09:55 AM
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
Private Sub Update1_Click()
Call OpenUserForm("UsrFrmGetFile")
End Sub

and

Option Explicit
Sub OpenUserForm(sForm As String)
sForm.Show
End Sub


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
07-04-2012, 10:19 AM
' 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

imellor
07-04-2012, 11:43 AM
' 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

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
07-04-2012, 12:32 PM
Assuming that sForm exists already ...


Option Explicit
Sub OpenUserForm(sForm As String)
UserForms (sForm).Show
End Sub


should work

Paul

imellor
07-04-2012, 01:07 PM
Assuming that sForm exists already ...


Option Explicit
Sub OpenUserForm(sForm As String)
UserForms (sForm).Show
End Sub


should work

Paul

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

Ian

Aussiebear
07-04-2012, 03:19 PM
try the following;

Option Explicit
Sub OpenUserForm(sForm As String)
Userform (SForm).Show
End Sub

Kenneth Hobs
07-04-2012, 03:35 PM
Did you need to pass a string to open the userform? Have you considered just doing it in the commandbutton?

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

Bob Phillips
07-04-2012, 03:51 PM
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
07-04-2012, 05:06 PM
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
07-04-2012, 05:45 PM
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:


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




Paul

Kenneth Hobs
07-04-2012, 06:36 PM
Chip Pearson's routine uses the Add concept that xld used.

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

Bob Phillips
07-05-2012, 12:35 AM
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
07-05-2012, 03:51 AM
Did you need to pass a string to open the userform? Have you considered just doing it in the commandbutton?

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub


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

Ian

imellor
07-05-2012, 03:54 AM
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