Populating several ComboBoxes from One Procedure

L

LScoot

I have several date ComboBoxes in my User Form which I would like to populate
by calling one sub. Below is my code but it doesn't seem to be working. The
MonthComboBox variable is passed correctlly but when I use it to add the
ComboBox it give me an error. Any suggestions would be greatly appreciated.

Thanks!!!

Public Sub NewProspectForm_CommandButton_Click()

' Dim SubToCall As String

Dim MonthComboBox(1 To 3) As String
MonthComboBox(1) = "AddNewProspect.ApplicationDateMonth_ComboBox"
MonthComboBox(2) = "AddNewProspect.EstimatedFundingMonth_ComboBox "
MonthComboBox(3) = "AddNewProspect.LoanStatusMonth_ComboBox"
For m = 1 To 3
Call FillMonthValues(MonthComboBox(m))
Next m

End Sub


Sub FillMonthValues(MonthComboBox)

' Fill values for Application Date Month

MonthComboBox.RowSource = ""
MonthComboBox.AddItem "01"
MonthComboBox.AddItem "02"
MonthComboBox.AddItem "03"
MonthComboBox.AddItem "04"
MonthComboBox.AddItem "05"
MonthComboBox.AddItem "06"
MonthComboBox.AddItem "07"
MonthComboBox.AddItem "08"
MonthComboBox.AddItem "09"
MonthComboBox.AddItem "10"
MonthComboBox.AddItem "11"
MonthComboBox.AddItem "12"

End Sub
 
J

Jezebel

You need to read up a little on variables and data types. Here, you're
getting mixed up between names and objects.

For starters, you should always specify your argument data types explicitly.
If you did that, VBA would haver told you the probem: Your FillMonthValues
function expects a combobox as an argument; but you are sending it a string.
Try something along these lines --

Public Sub NewProspectForm_CommandButton_Click()

FillMonthValues cbo:=AddNewProspect.ApplicationDateMonth_ComboBox
FillMonthValues cbo:=AddNewProspect.EstimatedFundingMonth_ComboBox
FillMonthValues cbo:=AddNewProspect.LoanStatusMonth_ComboBox

End Sub

Sub FillMonthValues(cbo As ComboBox)

With cbo
.AddItem "01"
.AddItem "02"
:
End With

End Sub


Separately, if you do want to assign an object to a variable (not necessary
in this case), you have to use the SET keyword --

Dim MonthComboBox(1 To 3) As ComboBox

set MonthComboBox(1) = AddNewProspect.ApplicationDateMonth_ComboBox
 
L

LScoot

Thank you. I adjusted my code according to your suggestion and it worked
perfectly. As you can see, I am new to VBA so I really appreciate your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top