UserForm_Initialize() is automatically called when a form variable is accessed in other module

Question

I am trying to get value of some publicly declared userform variable into other module written in other sheets.

first myform.show function initialize the form and set i = 0. But when I closed the form using x button and used the queryclosed function to update the value of i and control return to Myform.val = 10 line then userform_initialize() function is again called and the condition becomes false becoze it again set the i to 0. I don't know why this is happening. Any help please.

my sheet module code is as follows:

Sub myModule()
    Myform.Show
    If Myform.val=10 then
        msg "Hi"
    End if
End sub

and Myform code is as follows:

Public i as integer

Private Sub UserForm_Initialize()        
    i = 0
End sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then
        i = 10
    End If
End Sub

I want the above if condtion to be true if form is closed using x button and false if form is closed using commandbutton.

score 0 · Accepted Answer · answered Jul 20 '19 at 14:55

0

I'd like to suggest a few changes. All are not necessary, but it'll help you keep the code neater.
First, use an instance of the form, rather than the form. Second, use properties instead of global variables. Third, if you unload your form, you can't access its values anymore.

The module

Sub myModule()
    Dim MyFormInstance As MyForm

    Set MyFormInstance = New MyForm
    MyFormInstance.Show
    If MyFormInstance.Val = 10 Then
        MsgBox "Hi"
    End If
    Unload MyFormInstance
End Sub

And the form

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then
        Cancel = 1
        Me.Hide
        i = 10
    End If
End Sub

Property Get Val() As Integer
    Val = i
End Property

Private Sub btnClose_Click()
    i = 20
    Me.Hide
End Sub

answered Jul 20 '19 at 14:55

Sam

5,424
1
18
33

`Dim MyFormInstance As MyForm`? This is new to me, please link me to the documentation on this. I'm curious. – JvdV Jul 20 '19 at 15:04
@JvdV, a form is in many aspects identical to a class module. [Not documentation](https://learn.microsoft.com/en-us/office/vba/access/concepts/forms-design/create-multiple-instances-of-a-form), but still useful – Sam Jul 20 '19 at 15:32
@JvdV … a UserForm is nothing other than a *class* and you can/should use it as such (instead of referencing the default instance); you might get some references at [Destroy a modeless Userform instance properly](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly) – T.M. Jul 20 '19 at 15:35
@ Hi sam first of All vary thanks a lot , your answer solve the problem. i did not do much changes just add cancel =1 and me.hide line in query response function just like yours and its working fine just like i want . second i just check the if statement in given problem was wrong.in if condition instead of myform.val it was myform.i...but anyhow you solve it..thanks....but still i dont know why myform.i in my original code is calling the userform_initialize function...if you can point out the reason it will be helpful.. – Akash Yadav Jul 20 '19 at 17:58
1

When you closed the form, the variable holding it was reset. Since you used implicit instantiation, it was created from scratch again. When you changed to `.Hide`, all that changed. The variable is never reset, and you have it all available. – Sam Jul 20 '19 at 18:04

UserForm_Initialize() is automatically called when a form variable is accessed in other module

1 Answers1