Pass value from combo box to a variable

[zwieback89]

Hello,

Scenario:

On opening of the excel macro file, a user form loads.
1. User selects a period from the drop down box which is populated from a range in the excel sheet called "Month".
2. User clicks on the next button "Import Finance" to import data from 1 source file. Separate Module created to import the Finance data.
3. User clicks on the next button "Import Lines" to import data from 2nd source file.
Separate Module created to import the Lines data.
4. User clicks on the next button to compare the data from imported from step 2 and step 3. Separate Module to compare the data.

While all the this is going on, the form is visible and never unloads or hides. My question is how can I grab the month from the combo box into a variable to pass to the variable in the module in Step 4?

Thanks,

[Leith Ross]

Hello zwieback89,

This Code example assumes the name of the ComboBox to be "ComboBox1" on the UserForm. Pass the string variable "sMonth" as an argument to a procedure in your module.

Please Login or Register  to view this content.

[zwieback89]

Thanks for response. The comboBox is on the UserForm. On which event associated to the ComboBox, do I grab the Month chosen by the user?

I currently have it on the Change event.

Please Login or Register  to view this content.

Public Function to return the month to the procedure that needs it. However, it seems I enter into an infite loop and the chosen month appears as a Message the whole time. What makes this go into the infinite loop?

Please Login or Register  to view this content.

Procedure that needs the above month:

Please Login or Register  to view this content.

[snb]

You won't need a variable if you add a button in the userform to do the comparison:

Please Login or Register  to view this content.

[zwieback89]

Thanks for the tip. I was able to get the value by referencing to the Form combo box and use it according. That eliminated the need to use of a function.

Thanks to all for you suggestions.