Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
I want a generic way to show a UserForm, which I coded as
Public Sub displayForm(form As UserForm, mode As Integer) Debug.Assert mode = vbModal Or mode = vbModeless Load form form.Show mode End Sub The method fails,as the UserForm object does not support the Show method. My question is why does the 'actual' form, which *is* a UserForm, work? KeyMgtForm.Show vbModeless ----------- works fine So, is there another object that I can cast the UserForm to that will work? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
Eric,
Change the datatype for form to Object and it will work. Public Sub displayForm(form As Object, mode As Integer) When calling your form you create (and pass) an instance of the class KeyMgtForm not of the class Userform -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Eric wrote : I want a generic way to show a UserForm, which I coded as Public Sub displayForm(form As UserForm, mode As Integer) Debug.Assert mode = vbModal Or mode = vbModeless Load form form.Show mode End Sub The method fails,as the UserForm object does not support the Show method. My question is why does the 'actual' form, which is a UserForm, work? KeyMgtForm.Show vbModeless ----------- works fine So, is there another object that I can cast the UserForm to that will work? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
How about something like this (add you Mode part)
Public Sub displayForm(form As String) VBA.UserForms.Add(form).Show End Sub Sub ShowForm() displayForm "MyUserform" End Sub -- Regards, Tom Ogilvy "Eric" wrote in message k.net... I want a generic way to show a UserForm, which I coded as Public Sub displayForm(form As UserForm, mode As Integer) Debug.Assert mode = vbModal Or mode = vbModeless Load form form.Show mode End Sub The method fails,as the UserForm object does not support the Show method. My question is why does the 'actual' form, which *is* a UserForm, work? KeyMgtForm.Show vbModeless ----------- works fine So, is there another object that I can cast the UserForm to that will work? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
Hi Eric,
Public Sub displayForm(form As Object, mode As FormShowConstants) Load form form.Show mode End Sub MP "Eric" a écrit dans le message de k.net... I want a generic way to show a UserForm, which I coded as Public Sub displayForm(form As UserForm, mode As Integer) Debug.Assert mode = vbModal Or mode = vbModeless Load form form.Show mode End Sub The method fails,as the UserForm object does not support the Show method. My question is why does the 'actual' form, which *is* a UserForm, work? KeyMgtForm.Show vbModeless ----------- works fine So, is there another object that I can cast the UserForm to that will work? TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
keepITcool wrote:
When calling your form you create (and pass) an instance of the class KeyMgtForm not of the class Userform I'm not sure that's what the OP wants but it raises an interesting point i.e. that the Userform interface class has no Show method: Sub demo() Dim frm1 As VBAProject.UserForm1 Set frm1 = New VBAProject.UserForm1 MsgBox TypeOf frm1 Is VBAProject.UserForm1 MsgBox TypeOf frm1 Is MSForms.UserForm ' UserForm1 has a Show method frm1.Show vbModeless frm1.Hide Dim frm As MSForms.UserForm Set frm = frm1 MsgBox TypeOf frm Is VBAProject.UserForm1 MsgBox TypeOf frm Is MSForms.UserForm ' Userform interface class ' does not have a Show method: ' frm.Show vbModeless ' <<< error Load frm With VBA.UserForms Set frm1 = .Item(.Count - 1) End With frm1.Show vbModeless End Sub The UserForm interface has no Show method and that is why the OP's code raises a run-time error. Using As Object as a parameter, when As (interface) is available, requires additional validation code (among other reasons), so how about this variation on Tom's suggestion: Public Sub displayForm( _ ByVal form As UserForm, _ Optional ByVal mode As FormShowConstants = vbModal _ ) ' (code to validate mode) VBA.UserForms.Add(TypeName(form)).Show mode End Sub Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
Hi Jamie
I'm not sure your code is very efficient: Passing the form byval creates 2 instances of the interface class (both in the caller and in the callee). Check vba.userforms before the line: VBA.userforms.add Since you're not really using the interface class object, I think passing a string is more logical. Plus I think it should be wrapped in a function :) My function needs additional handling for calling existing forms in different modes.. but alas. Sub demoS() Dim ufs(2) As Object Set ufs(0) = showform("UserForm1", vbModeless) Set ufs(1) = showform("UserForm1", vbModeless) Set ufs(2) = showform("UserForm1", vbModeless, True) ufs(0).Left = 100 ufs(1).Left = 400 ufs(0).Caption = "ZERO" ufs(1).Caption = "UNO" ufs(2).caption = "DUE" End Sub Function showform(formclass As String, _ Optional ByVal mode As FormShowConstants = vbModal, _ Optional ByVal useExisting As Boolean) As Object Dim uf As Object If useExisting Then For Each uf In VBA.UserForms If TypeName(uf) = formclass Then Exit For Next End If On Error GoTo theEND If ObjPtr(uf) Then Set showform = uf Else Set showform = VBA.UserForms.Add(formclass) End If showform.Show mode theEND: End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jamie Collins wrote : so how about this variation on Tom's suggestion: Public Sub displayForm( _ ByVal form As UserForm, _ Optional ByVal mode As FormShowConstants = vbModal _ ) ' (code to validate mode) VBA.UserForms.Add(TypeName(form)).Show mode End Sub Jamie. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm usage
keepITcool wrote:
I'm not sure your code is very efficient: Passing the form byval creates 2 instances of the interface class (both in the caller and in the callee). Worse the unreferenced object created in the caller doesn't get released until the project ends e.g. press the reset button in the VBE or re-using the global instance of userform1 :-( Of course, passing ByRef makes no difference. Since you're not really using the interface class object, I think passing a string is more logical. I guess I'd reluctantly go for the latebound call using Object. However, I think if the OP's has a genuine need for a generic routine to show a userform (rather than merely posing an interesting theoretical question), I would expect them to prefer the classname as a string in the Userforms.Add method. I can't quite see it - element names stored in a database, perhaps? Now, can someone tell me why the Show method isn't in the interface... Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
100% cpu usage | Excel Discussion (Misc queries) | |||
Access from add_in userform to main template userform.... | Excel Programming | |||
Linking userform to userform in Excel 2003 | Excel Programming | |||
SQL - TOP 1 Usage | Excel Programming | |||
countif usage ? | Excel Programming |