Combobox Automation

[mllex]

My goal is to have a combo box appear when an Excel spreadsheet is opened, and to have the user select from the list. Each selection would run a different macro. This is what I have thus far.

Private Sub Workbook_Open()
Load frmTestDialog
frmTestDialog.Show
End Sub

Dim msg As String, i As Integer
msg = ""
With frmTestDialog.ComboBox1
ComboBox1.ColumnCount = 12
'# of dropdown rows in Combobox
ComboBox1.RowSource = "a1001: a1013"
'Fill ComboBox with range data
ComboBox1.ControlSource = "a1000"
'place the Combobox in cell a1000
For i = 0 To .ComboBox1 - 1
If .Selected(i) Then
msg = msg & .List(i) & Chr(13)
End If
Next i
End With
MsgBox msg, , "Selected items in Combobox1"
End Sub


The form loads, but the combobox syntax is not working. Does anyone have some sample code along these lines? It would be much appreciated.

Basically, I am looking to present different data entry variables to the user depending on their combobox selection.

 

[RobBroekhuis]

You don't specify where you run into problems. One obvious thing I see is that once you're inside the with construct,
you shouldn't use the ComboBox1 object name anymore, i.e.

With frmTestDialog.ComboBox1
.ColumnCount = 12
'# of dropdown rows in Combobox
.RowSource = "a1001: a1013"
'Fill ComboBox with range data
.ControlSource = "a1000"
'place the Combobox in cell a1000
For i = 0 To .ComboBox1 - 1
If .Selected(i) Then
msg = msg & .List(i) & Chr(13)
End If
Next i
End With

Also, I doubt you really want 12 columns - you probably want 12 rows. If the entries in the combobox will be static (i.e., not change very often), you're probably better off filling the list directly from your VBA code using the AddItem method rather than using the .rowsource property.
The .controlsource property does not place the combobox in a particular place on your sheet - it just puts the value that the user selects there.
Think about it, come back with more specific questions, and we'll help you further along!
Rob

 

[mllex]

Thank you. Yes, the data is static.

The following code successfully loads the form,

Private Sub Workbook_Open()
Load frmTestDialog
frmTestDialog.Show
End Sub


The following code is behind Combobox1 on Userform frmTestDialog.

I have not been successful in getting the combo box on the form to activate to show the intended list:


Dim msg As String, i As Integer
msg = ""

With frmTestDialog.ComboBox1
'# of dropdown rows in Combobox
.ColumnCount = 5

'Fill ComboBox with range data
.AddItem = Apples
.AddItem = Bananas
.AddItem = Pears
.AddItem = Grapes
.AddItem = Oranges

'place the Combobox value in cell a1000
.ControlSource = "a1000"

For i = 1 To 5
If .Selected(i) Then
msg = msg & .List(i) & Chr(13)
End If

Next i
End With
MsgBox msg, , "Selected items in Combobox1"

End Sub


I copied some code from another site to get me started.
Where am I going wrong?

Additionally, I want to run a different macro for each selection that the user makes. What would this could look like?

If ComboBox1.Selection=Apples Then Run Macro X
Else If ComboBox1.Selection=Bananas Then Run Macro Y
Else If ComboBox1.Selection=Pears Then Run Macro Z
'etc., etc.
Else
End If
End Sub

 

[mllex]

Ok, some progress. I get a combobox to display the contents properly , but the case statements do not work.
Any suggestions?

Also, I am confused as to what code should go into the userform initialize procedure, and what code should underlay the combobox?


Private Sub ComboBox1_Change()

ComboBox1.Font.Size = 12
ComboBox1.Font.Bold = True
ComboBox1.DropDown

Select Case ComboBox1.Value

Case 0 'Apples

MsgBox Msg = "Apples", vbOKOnly

Case 1 'Bananas
MsgBox Msg = "Bananas", vbOKOnly

Case 2 'Pears
MsgBox Msg = "Pears", vbOKOnly

Case 3 'Grapes
MsgBox Msg = "Grapes", vbOKOnly

Case 4 'Oranges
MsgBox Msg = "Oranges", vbOKOnly

End Select


End Sub



Private Sub UserForm_Initialize()
ComboBox1.AddItem "Apples" 'ListIndex=0
ComboBox1.AddItem "Bananas" 'ListIndex=1
ComboBox1.AddItem "Pears" 'ListIndex=2
ComboBox1.AddItem "Grapes" 'ListIndex=3
ComboBox1.AddItem "Oranges" 'ListIndex=4

End Sub

 

[RobBroekhuis]

I'm assuming (since the combobox is now displaying properly) that you got rid of the .columncount=5 (you don't need to specify up-front how many items will be in the combobox).
As for processing the combobox input:

You wouldn't normally process the input in the _change event for the combobox, unless there is something else that needs to happen when the value changes. Otherwise, you'd wait until the OKButton_click event to worry about the combobox (unless your userform is an unusual one without an OK button).
The value property for the combobox does not return the listindex of the selection - it returns the selection itself (a string value). So instead of your case statement, just use

MsgBox combobox1.value, vbOKOnly

Give it a try!
Rob

 

[mllex]

Please pardon my lack of experience with VBA.

I do want something else to happen when the value in the combobox changes, but to get me started I am just using a message box to see if my initial code is working.

Am I correct in that I need to use the initialize procedure to load the items into my combobox list? The list is static.

Then should I use a ComboBox1_Change procedure to run further macros based upon the users choice in the combobox? If the user chooses, say, "Apples", how exactly do I invoke the Apples macro? Do I need to create a public procedure for this? What is the code in the ComboBox1_Change procedure? Sub (Name of apples procedure)?

I keep getting a message box that reads "false". ? How do I make certain that when the combobox is displayed to the user that the initial value = "" ? It looks to me that the first time I run the initilize procedure the value defaults to Apples, the second time to Bananas, the third time to Pears, etc. So if I select anything else in the combobox list, I get a Msgbox that reads "False".

Private Sub UserForm_Initialize()

ComboBox1.AddItem "Apples" 'ListIndex=0
ComboBox1.AddItem "Bananas" 'ListIndex=1
ComboBox1.AddItem "Pears" 'ListIndex=2
ComboBox1.AddItem "Grapes" 'ListIndex=3
ComboBox1.AddItem "Oranges" 'ListIndex=4

ComboBox1.Font.Size = 13
ComboBox1.Font.Bold = True
ComboBox1.DropDown

ComboBox1.Value = ""

End Sub


Private Sub ComboBox1_Change()

If ComboBox1.Value = "Apples" Then
MsgBox Msg = "Apples", vbOKOnly
'actually invoke another macro

ElseIf ComboBox1.Value = "Bananas" Then
MsgBox Msg = "Bananas", vbOKOnly
'actually invoke another macro

ElseIf ComboBox1.Value = "Pears" Then
MsgBox Msg = "Pears", vbOKOnly
'actually invoke another macro

ElseIf ComboBox1.Value = "Grapes" Then
MsgBox Msg = "Grapes", vbOKOnly
'actually invoke another macro

ElseIf ComboBox1.Value = "Oranges" Then
MsgBox Msg = "Oranges", vbOKOnly
'actually invoke another macro

Else
MsgBox Msg = "Try Again", vkOKOnly
'actually invoke another macro

End If
End Sub

 

[RobBroekhuis]

You get the 'false' because that is the value of the expression
msg="oranges" (etc),
i.e., msg is not equal to oranges. You can leave the 'msg=' part out.
You can write your macros to handle the different cases right in the userform code, as private or regular subs (or you could refer to a public sub in a different module, if you need to call the sub from other places as well).
To run the macro (say you call it "sub DoApples&quot, you can simply use the code

DoApples

or

Call DoApples

Does that help?
Rob

 

[mllex]

Oh, Okay. Thank you very much. I really appreciate your taking the time to help me.

 

[mllex]

Ok..I am still trying. The initialize procedure works fine. The ComboBox1_Change() procedure does not. When I make a selection nothing happens. Sometimes the Visual Basic Editor remains open and "Private Sub ComboBox1_Change" is highlighted. I checked the public procedures and they all work independently. What am I doing wrong?


Private Sub UserForm_Initialize()

ComboBox1.SetFocus
ComboBox1.AddItem "Apples"
ComboBox1.AddItem "Bananas"
ComboBox1.AddItem "Pears"
ComboBox1.AddItem "Grapes"
ComboBox1.AddItem "Oranges"

ComboBox1.Font.Size = 13
ComboBox1.Font.Bold = True
ComboBox1.DropDown
End Sub



Private Sub ComboBox1_Change()

If ComboBox1.Value = "Apples" Then
UserForm2.Hide
Call DoApples


ElseIf ComboBox1.Value = "Bananas" Then
UserForm2.Hide
Call DoBananas

ElseIf ComboBox1.Value = "Pears" Then
UserForm2.Hide
Call DoPears

ElseIf ComboBox1.Value = "Grapes" Then
UserForm2.Hide
Call DoGrapes

ElseIf ComboBox1.Value = "Oranges" Then
UserForm2.Hide
Call DoOranges

Else
End If

End Sub