CODEWITHSUNDEEP
vbaexceluserform
Synergist
Synergist

Reputation: 547

Initializing module variables in a UserForm

I'm trying to use a variable that was initialized in a UserForm. But the simple example below just generates an empty MessageBox, regardless of what I write in the UserForm as the input.

Public X As String
Private Sub Module1()
  InputForm.Show
  MsgBox(X)
End Sub

InputForm:

Private Sub CommandButton_Done_Click()
  X = InputForm.ListBox1.Value
  Unload InputForm
End Sub

I'm new to VBA and I'm not sure how to debug the problem

Upvotes: 2

Views: 2403

Answers (4)

Cleofe Righetti
Cleofe Righetti

Reputation: 1

I've got the same problem. I found that context is different between "where is stored the main code" and "where is stored the form code"

so, I ended up in this way (I'll apply to your code) and IT WORKED BOTH DIRECTIONS

    Private Sub CommandButton_Done_Click()
      ThisWorkbook.X = InputForm.ListBox1.Value
      Unload InputForm
    End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

There are many problems waiting to bite you in the rear end down that road. This article describes everything in details - here's the crux of it: flip the dependencies around, don't let the form run the show.

Create a new class module - that will be your model:

'class ExampleModel (class module)
Option Explicit

Private Type TModel
    Foo As String
    'other instance members
End Type

Private this as TModel

Public Property Get Foo() As String
    Foo = this.Foo
End Property

Public Property Let Foo(ByVal value As String)
    this.Foo = value
End Property

'other properties...

The members of this class will be everything the form needs to know. IOW that's where your X belongs.

The form is your view - it needs to know about the model, so you expose it to the outside world:

'class InputForm (UserForm module)
Option Explicit
Private m As ExampleModel ' at module-level, because you'll eventually want it to be WithEvents at one point

Public Property Get Model() As ExampleModel
    Set Model = m
End Property

Public Property Set Model(ByVal value As ExampleModel)
    Set m = value
End Property

All the form controls do, is manipulate the Model properties. So if you want to have m.Foo = ListBox1.Value, then you handle ListBox1.Change and do exactly that:

Private Sub ListBox1_Change()
    m.Foo = ListBox1.Value
End Sub

Now all the "OK" button needs to do, is close the form:

Private Sub CommandButton_Done_Click()
    Me.Hide
End Sub

Notice it's hiding the form, not Unloading anything. The form is an object, which means it was created by something, somewhere - and that something will not be expecting the object it created to spontaneously self-destruct. So you never Unload Me, and even more never (if that's a thing) do Unload TheFormClassName, because that would unload the form's default instance, and there's nothing anywhere that can guarantee that your form is always going to be shown off the default instance: it's a class like any other, it wants to be instantiated.

In order to avoid self-destructing the form, you need to handle the QueryClose event:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

Here, we cancel the closing/destruction of the form when the user clicks the red [X] button in the upper-right corner, and hide the current instance (the Me keyword always refers to the current instance of the class).

So what does the calling code look like now?

'module Module1 (standard module)
Option Explicit

Private Sub Test()
    Dim m As ExampleModel
    Set m = New ExampleModel
    Dim view As InputForm
    Set view = New InputForm
    Set view.Model = m
    view.Show
    MsgBox m.Foo
End Sub

Now if you want you can add logic in the Model property setter to pre-populate your form controls with values controlled by the calling procedure. Or you can have some IsCancelled property on the model, that's set when some CancelButton is clicked, and then the calling code can determine whether the form was cancelled, and conditionally show that MsgBox.

Global variables needed: 0.

Upvotes: 2

urdearboy
urdearboy

Reputation: 14580

Your first sub opens the userform, thus your input box will be blank so you should expect a blank msgbox (Unless you have the box auto-populating).

A good practice (in my experience) is to work with your variables before Unloading the Userform, thus, you should handle them in your CommandButton_Done_Click()

1) Sub to Open Userform
2) Seperate Sub (Initialize) to control what happens when the userform is open (Control entry type, auto-populate fields, etc)
3) Final sub (Done_Click) to control what happens before the unload... (Validate input is string/int/etc, place inputs in cells, etc.)

Private Sub Module1()
  InputForm.Show
End Sub

Private Sub CommandButton_Done_Click()
  Msgbox InputForm.ListBox1.Value
  Unload InputForm
End Sub

Upvotes: 1

Taylor Raine
Taylor Raine

Reputation: 578

You need specify that X is in the context of the module in which X exists. For example, if X exists as a module variable in module Module1 you could change the CommandButton_Done_Click() subroutine to be

Private Sub CommandButton_Done_Click()
  Module1.X = InputForm.ListBox1.Value
  Unload InputForm
End Sub

It is important to note that you cannot declare the module which holds X as private if you want this to work.

Upvotes: 3

Related Questions