VBA Excel Userform combo box how to Programmatically add items

Hi

To display the month names in a combobox, try this

create a combobox and name it 'cboMonths'

Place this code on the userform module.

Private Sub UserForm_Initialize()
    
    Dim Months(1 To 12) As String
    Dim i               As Long
    
    For i = 1 To 12
        Months(i) = MonthName(i)
    Next
    
    With Me.cboMonths
        .List = Months
        .ListIndex = Month(Date) - 1
        .Style = fmStyleDropDownList
    End With
    
End Sub

Select allOpen in new window

Kris

Kris,

Thank you for your input.  The problem I'm having is not so straight forward as your solution is.  My problem is that I'm trying to use an existing function that creates both the form and the combo boxes while the code is executing.  The function does everything I need it to do except populate the combo boxes with a list of numbers for the users to choose from.

The semi-working function is in the Excel workbook with my original posting.  The function is named "GetCombo" .  The subroutine "Demo1()", also in the workbook,  calls the function and passes it an array from which the function builds the combo boxes.  This all works. The part I can't get to work is the part where the combo boxes placed on the form have a drop-down list giving the user a choice of numbers from -2 to 2.

Sorry again - I'm probably making this too confusing.

Kris,

Thank you for your input.  The problem I'm having is not so straight forward as your solution is.  My problem is that I'm trying to use an existing function that creates both the form and the combo boxes while the code is executing.  The function does everything I need it to do except populate the combo boxes with a list of numbers for the users to choose from.

The semi-working function is in the Excel workbook with my original posting.  The function is named "GetCombo" .  The subroutine "Demo1()", also in the workbook,  calls the function and passes it an array from which the function builds the combo boxes.  This all works. The part I can't get to work is the part where the combo boxes placed on the form have a drop-down list giving the user a choice of numbers from -2 to 2.

Sorry again - I'm probably making this too confusing.

Please show us the code you use to create the combobox.

Sorry I didn't post this earlier.  Here is the complete module with both the Demo1() subroutine that calls the function and the function GetCombo.

Option Explicit

'Passed back to the function from the UserForm
Public GETOPTION_RET_VAL As Variant


Sub Demo1()
    Dim Ops(1 To 12) As String
    Dim i As Integer
    Dim UserChoice As Variant
'   Create an array of month names
    For i = 1 To 12
        Ops(i) = Format(DateSerial(1, i, 1), "mmmm")
    Next i
    UserChoice = GetCombo(Ops, 1, "Select a month")
    If UserChoice = False Then
        Range("A6") = ""
    Else
        Range("A6") = Ops(UserChoice)
    End If
End Sub

Function GetCombo(OpArray, Default, Title)
    Dim TempForm  'As VBComponent
    Dim NewOptionButton As MSForms.OptionButton
    Dim NewCommandButton1 As MSForms.CommandButton
    Dim NewCommandButton2 As MSForms.CommandButton
    Dim NewComboBox As MSForms.ComboBox
    Dim TextLocation As Integer
    Dim X As Integer, i As Integer, TopPos As Integer
    Dim MaxWidth As Long
    Dim WasVisible As Boolean
    
'   Hide VBE window to prevent screen flashing
    'Application.VBE.MainWindow.Visible = False

'   Create the UserForm
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
    TempForm.Properties("Width") = 800
    
'   Add the OptionButtons
    TopPos = 4
    MaxWidth = 0 'Stores width of widest OptionButton
    For i = LBound(OpArray) To UBound(OpArray)
        Set NewComboBox = TempForm.Designer.Controls.Add("forms.combobox.1")
        With NewComboBox
            .Width = 50
            .Name = OpArray(i)
            .Value = OpArray(i)
            .AddItem 2
            .AddItem 1
            .AddItem 0
            .Height = 15
            .Left = 8
            .Top = TopPos
            .Tag = i
            .AutoSize = False
            If .Width > MaxWidth Then MaxWidth = .Width
        End With
        TopPos = TopPos + 15
    Next i
    
'   Add the Cancel button
    Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewCommandButton1
        .Caption = "Cancel"
        .Height = 18
        .Width = 44
        .Left = MaxWidth + 12
        .Top = 6
    End With

'   Add the OK button
    Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewCommandButton2
        .Caption = "OK"
        .Height = 18
        .Width = 44
        .Left = MaxWidth + 12
        .Top = 28
    End With

'   Add event-hander subs for the CommandButtons
    With TempForm.CodeModule
        X = .CountOfLines
        .InsertLines X + 1, "Sub CommandButton1_Click()"
        .InsertLines X + 2, "  GETOPTION_RET_VAL=False"
        .InsertLines X + 3, "  Unload Me"
        .InsertLines X + 4, "End Sub"
        
        .InsertLines X + 5, "Sub CommandButton2_Click()"
        .InsertLines X + 6, "  Dim ctl"
        .InsertLines X + 7, "  GETOPTION_RET_VAL = False"
        .InsertLines X + 8, "  For Each ctl In Me.Controls"
        .InsertLines X + 9, "    If ctl.Tag <> """" Then If ctl Then GETOPTION_RET_VAL = ctl.Tag"
        .InsertLines X + 10, "  Next ctl"
        .InsertLines X + 11, "  Unload Me"
        .InsertLines X + 12, "End Sub"
    End With
    
'   Adjust the form
    With TempForm
        .Properties("Caption") = Title
        .Properties("Width") = NewCommandButton1.Left + NewCommandButton1.Width + 10
        If .Properties("Width") < 160 Then
            .Properties("Width") = 160
            NewCommandButton1.Left = 106
            NewCommandButton2.Left = 106
        End If
        .Properties("Height") = TopPos + 24
    End With

'   Show the form
    VBA.UserForms.Add(TempForm.Name).Show

'   Delete the form
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
    
'   Pass the selected option back to the calling procedure
    GetCombo = GETOPTION_RET_VAL
End Function

Select allOpen in new window

Why are you creating the form during run time rather than during design time?

Maybe I'm taking the wrong approach.  With my non-existant experience with user forms this was the first idea I had.  

My basic problem is I need to get user input for each of the months that they are analyzing but I will not know how many months over how many years a user is looking at until after the program is already running.  I won't know how many combo boxes to provide the user until they tell me (in the program) what they want to analyze.  Currently I capture the user's input (which months they need to look at) in an array so I can pass this array to a function or another subroutine to generate the form.  Please let me know if I'm going in the wrong direction with this.

Try the attached spreadsheet. It creates as many userforms as you want.
MartinLiss.xlsm

MartinLiss Thank you for your input.  

Creating the form is not the problem.

I have a form and can populate it with as many combo boxes as the user needs.  My problem is fixing the function GetCombo so that each of the combo-boxes will display a drop down list containing the following list of values:
-2
-1
0
1
2

Okay, is this what you need?
MartinLiss.xlsm

MartinLiss thanks again for your response.

I can see how using the AddItem method you were able to add values to the drop down list of your combobox.  You included this code in the On_Click event.

Does the AddItem method only work when it is called by an event?

You can see in my code above I try to use the AddItem method when I create the combo box but the values do not show up in the form when it is displayed.

What am I missing here?

Thanks

I never actually tried your code so I don't know but you could try this.

Dim WithEvents NewComboBox As MSForms.ComboBox

In any case is there anything wrong with doing it my way?

Your way would be great it seems very straight forward but I don't know how many combo boxes will be on the form until I get user input.  When the user tells me  which months they want analyzed I need to provide a combo box for each of those months.  The user can choose anywhere from 1 to 20 months.

I've no experience with forms so most likely I'm making this way too hard.

How would you approach this?   How would you build a form with combo boxes but you don't know how many combo boxes you will need until after the code is running?

MartinLiss

I like how you think!  That should work and I never would have thought of that.

Sorry this has been such a long and twisted process - I should have stated my original question better.

I really thought I had a good solution.  Oh well.

Thanks again for your help!

As an academic exercise I'll take a look at your posted spreadsheet when I get a chance.

Gracias!

I was reminded that I had tried to run your code previously,but when I did try it I got this error.

And clicking 'debug' it highlights the following line. Can you tell me how to get around that?

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)

The original Function was designed to return a single variant from an option button (T/F, 0 or 1).  This to me would be an easy fix so I wasn't worried about it until I could get the combo box to work.

Two things you can do to avoid the error:

    
'   Add the OptionButtons
    TopPos = 4
    MaxWidth = 0 'Stores width of widest OptionButton
    For i = LBound(OpArray) To UBound(OpArray)
        Set NewComboBox = TempForm.Designer.Controls.Add("forms.combobox.1")
        With NewComboBox
            .Width = 50
            .Name = OpArray(i)
            .Value = 0
            .Height = 15
            .Left = 8
            .Top = TopPos
            .Tag = i
            .AutoSize = False
            If .Width > MaxWidth Then MaxWidth = .Width
        End With
        
        
        TopPos = TopPos + 15
    Next i

Select allOpen in new window

Is there any way to programmattically add event to combobox to already created user form ? I can see that there is a code to create new from along with its control and click event. I tried the samilar approach to add code to existing form but it gives error at userform.Codemodule row

appreciate your help
thanks.