Userforms: Use variable to show a loaded userform

[devhs]

Hi,

I have searched for hours trying to solve what should be a simple process. Please excuse me if I am missing something very obvious.

I have a userform (MTScoringForm) that shows modal. One of the button controls hides the MTScoringForm and opens a modeless form (frmOKModeless) to allow the user to directly edit a worksheet. Upon closing the modeless form I want the MTScoringForm to show again.

I use this same modeless form (frmOKModeless) to allow user interaction with the worksheet in other situations, requiring hiding and re-showing of other modal userforms. So I am trying to pass the initiating userform to the frmOKModeless as a variable.

If I use userforms.add (variable), it creates a new instance of the userform. To investigate, I created what should be a simple process (based on the advice from AlphaFrog: http://www.mrexcel.com/forum/excel-questions/710098-putting-userform-variable.html)

Sub TEST()[INDENT]Dim objUserForm As Object

Set objUserForm = MTScoringForm
Load objUserForm

ShowUserformA ("MTScoringForm")  'Pass as string[/INDENT]
[INDENT]ShowUserformB (objUserForm)        'Pass as object

[/INDENT]
End Sub

Sub ShowUserformA(Byval strShowA as string)[INDENT]
UserForms.Add(strShowA).Show[/INDENT]

End Sub


Sub ShowUserformB(ByVal objShowB As Object)[INDENT]
objShowB.Show[/INDENT]

End Sub

I can successfully test for the name of the objUserForm in the first "Test" procedure, and can pass the userform as a string to ShowUserformA. But that creates a new instance of the form, not a re-showing of the existing, loaded form. When I try to pass it as an object to ShowUserformB, I get a Run-time error '438' ("Object doesn't support this property"). Can anyone help me understand where I'm going wrong?

Thanks!

 

[shg]

Welcome to the board.

Looking no further than this,

ShowUserformB (objUserForm)        'Pass as object

... you can't put parens around the argument. That tells VBA to evalute the argument to a simple data type, which it cannot do.

The parens shouldn't appear in the prior line either, but in that case it's harmless; the literal string evaluates to a literal string.

 

[devhs]

OMG!! Thank you!! So simply yet so elusive.....

I've found lots of great material on this board, but this was my first post. What a fantastic service and resource this community provides!

 

[devhs]

P.S. I wanted to understand this better and found an excellent discussion of the concept by Floris Kleijne: For those interested in the rules governing the use of parens() in sub calls, check it out: ms access - What are the rules governing usage of brackets in VBA function calls? - Stack Overflow

 

[devhs]

One last question: Is there a way to pass the name of a loaded userform as a string and open that existing form without creating a new instance? (e.g. my ShowUserformA("MTScoringForm") example without using userforms.add method?

 

[bobgentry]

if you hide the form instead of unloading it, it will be preserved, along with anything you've entered into it. there's a few ways to do that. myself I tend to use the QueryClose event to determine exactly how it was closed (such as by the X button in the corner), and sometimes hide it instead of letting it actually close entirely.

 

[shg]

userforms("myForm").show

 

[devhs]

I tried that approach. But

userforms("myForm").show

gives me an error 13 'Type mismatch'. I found the only way was by calling

userforms.add("myForm")

, which seemed to create a second instance of the form.

 

[mikerickson]

You could put this in the code module for frmOKModeless

' in frmOKModeless

Private Sub UserForm_Terminate()
    If 0 < UserForms.Count Then
        UserForms(UserForms.Count - 1).Show
    End If
End Sub