VBA Excel Userforms, Show, Hide

Question

I have a question regarding instances of userforms.

When a userform is constructed via an object in a module (e.g. set frm = new Userform2) can I hide it and restore it by the show method even when the sub is run until the end?

Let's say:

userform creation by sub1
entering values to the userform
hiding the userform with sub2
restore userform with all values by sub3 with the show method

I got really strange behavior when testing code with show and hide methods on module level or Userform Code but what I got finally to work is that using a global variable and the following code in a standard module:

Global frm As UserForm2
Option Explicit

Sub sub1()
Set frm = New UserForm2
    With frm
        .Show vbModeless
    End With
End Sub

Sub sub2()
    With frm
        .Hide
    End With
End Sub

Sub sub3()
    With frm
        .Show vbModeless
    End With
End Sub

It's often said that global variables should be avoided. Is it even possible here? Do I miss something?

What were the strange results? I'd code it similar, apart from declaring the variable at the module level like so Public/Private f As UserForm1 and maybe a sub for the showing part to ensure consistent "showing" It would be better maybe if the form also checked the existience of frm on opening, so if run from F5, and to make sure the sub2 and 3 have an object to operate on. — Nathan_Sav
– Nathan_Sav, Commented Mar 1, 2019 at 15:05
Why are you .Hideing at a Module Level, instead of as part of the UserForm? Also, if you just UserForm2.Show, you can show the existing instance, with no need to create a New UserForm2 at any point — Chronocidal
– Chronocidal, Commented Mar 1, 2019 at 15:08
This is a must read when showing/hiding UserForms: rubberduckvba.wordpress.com/2017/10/25/userform1-show — Ricardo Diaz
– Ricardo Diaz, Commented Mar 1, 2019 at 15:26
thanks Ricardo Diaz for posting the link. I read the article but it's hard for me to understand fully his approach which is more oop oriented. I stored it and will go over several times. But as he wrote unexpected behavior seems to happen sometimes depending how the code is structured — cekar
– cekar, Commented Mar 1, 2019 at 22:56

cekar · Accepted Answer · 2019-03-01 15:32:11Z

What I really want is to hide the userform but keep the instance with all values set before. Here is my original code and I now realized that I mixed things up and made and error. I had an "end" in a condition and this forces the UF not to show resp. killing all instances. @Chronocidal: In fact I hide within code within the UserForm. This is part of a button which transfers set data from UF to sheet. But I think that is not important where to hide or? Now it is working as I expected. Thanks to all of you

Global myfrm As FormFillInformation
Option Explicit

Sub InitUserFormGeneralInformation()
Dim chkfrm As Boolean
chkfrm = CheckFrmIsHidden
If chkfrm = True Then
    myfrm.Show vbModeless
    End '<<<<<========== completely wrong but overseen
Else
    Set myfrm = New FormFillInformation
    myfrm.Show vbModeless

End If

End Sub


Function CheckFrmIsHidden() As Boolean
Dim frm As Object
CheckFrmIsHidden = False
For Each frm In VBA.UserForms
 If frm.Name = "FormFillInformation" Then
    CheckFrmIsHidden = True
 End If
Next
End Function

Collectives™ on Stack Overflow

VBA Excel Userforms, Show, Hide

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related