Userform combobox list items + save userform values as variants in VBA Excel

Question

I am using Excel userforms for the first time and I'm having some trouble. Essentially, I want to ask my user for three values, and then save those values as variants in my vba code to use later. I initially set up my VBA code to get these three values using three separate input boxes, but I find that misspelling happens too often that way. Instead I want the user to just pick from a list of values.

Here's what the box looks like:

I just want the two combo boxes there to each have two options, and they will always be the same options, so I don't want to put them in a list on a worksheet, I want that to be part of the VBA code. My userform is named UserFormPYBTMT, and the combo boxes are named cboxlBT and cboxlMT, and the text box is tbxxlPY. This is what I have tried:

Public Sub UserFormPYBTMT_Initialize()

'fill combobox for BT
    userformPYBTMT.cboxlBT.AddItem ("BTChoice1")
    userformPYBTMT.cboxlBT.AddItem ("BTChoice2")

'fill combobox for MT
    userformPYBTMT.cboxlMT.AddItem ("MTChoice1")
    userformPYBTMT.cboxlMT.AddItem ("MTChoice2")

End Sub

---------------------

Public Sub btnxlOK_Click()

End Sub

Obviously I also have the OK button there (which I haven't written any code for), at which point I want VBA to save the combo box values as my variants and close the userform. The macro where I want to use these values looks like this:

Sub SATV5()

Dim IBPYSAT As Variant
Dim IBMTSAT As Variant
Dim IBBTSAT As Variant

'Show the user form where we get the inputs for PY, MT, BT
userformPYBTMT.Show    

IBPYSAT = userformPYBTMT.tbxxlPY.Value
IBMTSAT = userformPYBTMT.cboxlMT.Value
IBBTSAT = userformPYBTMT.cboxlBT.Value

If someone who has some knowledge of userforms and comboboxes could give me some pointers I would be extremely grateful. Thank you.

Could you clarify what the actual issue is? Also what do you mean by save? Do you use the values then discard, or are you trying to permanently save? — jcarroll
@jcarroll There are two broad issues, I suppose. How do I list the options for the comboboxes, since the code I used didn't actually work (When I open the userform the comboboxes are blank and don't show my two options), and how do I set the values of the comboboxes equal to my variant (That doesn't work either). I will in the future reuse the userform and at that time I want to get new values from the user to 'save' or set my variants to. — Lui
Try changing UserFormPYBTMT_Initialize() to UserForm_Initialize(), I don't think your code is even being called. As for saving values, there is no way to store values in variables once you close Excel. You'll need to save them to a database or worksheet. — jcarroll
Thanks, I'll try that. I don't want to store them as variables for a long period of time, only to use them in my other macro. I just want to temporarily set my variant equal to the value of the combobox. — Lui
@jcarroll Ok, the UserForm_Initialize() worked, which is great! But I don't understand why. If I had multiple user forms, would all of those start with Sub UserForm_Initialize()? It wouldn't matter that I named the userform differently? — Lui

Tehscript Tehscript · Accepted Answer · 2017-07-07T21:12:56

You should declare your variables as public variable and you should hide the userform instead of closing it. See below example:

Public IBPYSAT As String, IBMTSAT As String, IBBTSAT As String

Private Sub btnxlOK_Click()

'Show the user form where we get the inputs for PY, MT, BT

IBPYSAT = userformPYBTMT.tbxxlPY.Value
IBMTSAT = userformPYBTMT.cboxlMT.Value
IBBTSAT = userformPYBTMT.cboxlBT.Value

userformPYBTMT.Hide

Call MyMacroAfterClosingUserform

End Sub

Sub MyMacroAfterClosingUserform()
    MsgBox IBPYSAT & " is my Textbox value and " & IBMTSAT & " and " & IBBTSAT & " are my combobox values!"
    Unload userformPYBTMT 'now you can close userform after you are done with the variables!
End Sub

Userform combobox list items + save userform values as variants in VBA Excel

1 Answers