CODEWITHSUNDEEP
excelvba
Jim Fisher
Jim Fisher

Reputation: 45

VBA ComboBox Value in Module1

Hello I am trying to get the selected value from my ComboBox to work in a subroutine in Module1.

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "Monday"
        .AddItem "Tuesday"
    End With
End Sub

I'm basically trying to get the value I selected to be pasted onto "Sheet1". I can get this to work if I call "ComboBox1.value" in the UserForm1, but not Module 1 where it throws an error saying "ComboBox1.value" is not defined. I would appreciate any help!

Sub EmailGenerator()
   UserForm1.Show
   Worksheets("Sheet1").Range("O5").Value = ComboBox1.Value
End Sub()

Upvotes: 1

Views: 1313

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Try this approach, please:

    With UserForm1.ComboBox1
        .AddItem "Monday"
        .AddItem "Tuesday"
        .ListIndex = 0
    End With

    ActiveSheet.Range("I1").value = UserForm1.ComboBox1.value

No necessary to Show the form for doing that. But, if you do not select anything in combo, nothing will be returned in the cell. If you want to do that in consecutive steps, I would recommend to firstly check if the UserForm1.Visible = True. Only supposing that you want to manually change the combo value and only after that to collect values on the sheet.

But this approach is, at least, strange to me... I only tried to show you how it is possible, but I would not recommend it.

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

The form is an object: it's an instance of the class UserForm1 - one that you didn't explicitly create yourself (see UserForm1.Show for everything that entails), but it's an object nonetheless.

There's an instance of a ComboBox control named ComboBox1, living in that UserForm1 object, and exposed on the form's public interface (exactly as if it were a public property of the form itself!) - that's how you're able to access the control from outside the form's code-behind (note: that's the simplest way to do UI, but there are better, more scalable ways that don't break encapsulation like that and leave the form responsible for its controls and the rest of the code blissfully unaware of the layout of the UI and what the specific controls are, see link above).

VBA doesn't know what ComboBox1 you're referring to, unless you qualify that member call with the object it belongs to, using the . dot operator:

UserForm1.ComboBox1

You could have a UserForm2 that also has a ComboBox1 control, and there could be 20 worksheets, each with their own ComboBox1 control: they can't live in global scope, and they don't.

Thus:

Worksheets("Sheet1").Range("O5").Value = UserForm1.ComboBox1.Value

Or better:

Public Sub EmailGenerator()
   With New UserForm1
       .Show '<~ form is modal, execution is blocked here until form is closed.
       Worksheets("Sheet1").Range("O5").Value = .ComboBox1.Value
   End With
End Sub

Upvotes: 2

Related Questions