Creating variable in userform and moving it to a module

Question

I'm new to VBA and and am trying to finish this project by teaching myself. I'm trying to make a userform with a ComboBox drop down menu of months and a textbox to enter the year. I then want to have those values saved as variables and transferred back to the main module. I have referenced Assigning a variable directly from a userform and Passing variable from Form to Module in VBA, but what they did isn't working for me. This is what my form looks like.

My form is named Dates. The Combo box is Correct_Month; textbox is Correct_Year. I want to save these values in variables Month and Year.

This is what I have for the userform:

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub Correct_Month_Change()
Month = Correct_Month.Value
End Sub

Private Sub Correct_Year_Change()
Year = Correct_Year.Value
End Sub

Private Sub OkButton_Click()
'Message box forces a response
If Correct_Month.Text = "" Or Correct_Year.Value = "" Then
    MsgBox ("You must enter a month and year to continue")
    Exit Sub
End If
Unload Me
End Sub

Private Sub UserForm_Initialize()
Correct_Month.Clear
Correct_Month.List = Array("January", "February", "March", "April", "May", _ 
    "June", "July", "August", "September", "October", "November", _
    "December")
Correct_Year.Value = ""
End Sub

This is my module:

Private Sub Monthly_Report_Test()

'~~> more coding

    Dates.Show
    'something here to call variables over?
    driver.findElementByName("Dates").SendKeys Month
    driver.findElementByName("Dates").SendKeys Year

    Correct_Info = MsgBox("Is the following information correct?" & vbCrLf & vbCrLf & _
        "Report Month: " & Month & vbCrLf & vbCrLf & "Report Year: " & Year, _
        vbYesNoCancel, "Month & Year")

    If Correct_Info = vbCancel Then
        Exit Sub
    End If

'~~> more coding

End Sub

I've tried several different things and this is as close as I can get. Can anyone tell me what I need to do and why? Thank you so much!

[This answer](http://stackoverflow.com/a/43920726/1188513) gets you started with actual OOP code, no globals, no default form instance, no self-destructing objects. — Mathieu Guindon, May 11 '17 at 21:15

score 0 · Answer 1 · answered May 11 '17 at 21:23

0

In vba you can get the value of control (=your combo box) from userform by simply write the NameOfUserForm.NameOfControl

In your case - add to your module there lines :

Dim Var1, Var2
Var1 = Dates.Correct_Month.Value
Var2 = Dates.Correct_Year.Value

Now you can use the variables : Var1 & Var2 - as you want.

answered May 11 '17 at 21:23

user1598814

732
9
10

The problem with using the default instance and closing it with `Unload Me` is that as soon as the form destroys itself, its state is gone as well. The solution is to *not* unload the form (no self-destructing objects), use an actual instance (`New` it up yourself), and expose the state via properties - or better, via an interface that the form implements. That way the controls are encapsulated implementation details and there's no incestuous back-and-forth between the form and its callers. This answer would work for a buggy form that doesn't unload itself. Not the case with OP's code though. – Mathieu Guindon May 11 '17 at 23:58
He is new in VBA - and you offer things from OOP concept that are too complicated than he need. – user1598814 May 12 '17 at 03:34
Fair enough. That makes many more VBA questions to answer though, and many bad habits to undo later, when they *do* learn. – Mathieu Guindon May 12 '17 at 03:46

Creating variable in userform and moving it to a module

1 Answers1