Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2760
  • Last Modified:

VBA Excel Userform combo box how to Programmatically add items

I've attached an Excel workbook that contains a function that sort of works for the problem I'm trying to solve.  I have some experience with VBA but none with user forms.  

How can you add items to a combo box programmatically?

I need a user form to display combo boxes that represent months of the year.   Each combo box should give the user the following list of choices:  -2,-1,0,1,2

I found the shell of a solution on the web which I've modified as best as I can (attached).

The attached function adds months based on an array passed to it - I can do that.  I can't get the combo boxes to display the list of numbers shown above.

Ultimately the function should return the value selected by the user for each month that was passed to the function.

Any help would be much appreciated!
TestGetCombo.xlsm
0
dec789
Asked:
dec789
  • 10
  • 8
1 Solution
 
krishnakrkcCommented:
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

Open in new window


Kris
0
 
dec789Author Commented:
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.
0
 
dec789Author Commented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
Please show us the code you use to create the combobox.
0
 
dec789Author Commented:
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

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Why are you creating the form during run time rather than during design time?
0
 
dec789Author Commented:
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.
0
 
Martin LissRetired ProgrammerCommented:
Try the attached spreadsheet. It creates as many userforms as you want.
MartinLiss.xlsm
0
 
dec789Author Commented:
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
0
 
Martin LissRetired ProgrammerCommented:
Okay, is this what you need?
MartinLiss.xlsm
0
 
dec789Author Commented:
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
0
 
Martin LissRetired ProgrammerCommented:
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?
0
 
dec789Author Commented:
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?
0
 
Martin LissRetired ProgrammerCommented:
Could you simply add 20 comboboxes with data on the userform and set the Visible property for 19 or 20 of them to False initially?
0
 
dec789Author Commented:
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!
0
 
Martin LissRetired ProgrammerCommented:
As an academic exercise I'll take a look at your posted spreadsheet when I get a chance.
0
 
dec789Author Commented:
Gracias!
0
 
Martin LissRetired ProgrammerCommented:
I was reminded that I had tried to run your code previously,but when I did try it I got this error.
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)
0
 
dec789Author Commented:
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:
1. Choose the "Cancel" button on the form.
2. Change the value of the combo box to 0 or 1
In the code snippet below (Line #10) each new combo box is created and added to the form.  
Change the line:  .Value = OpArray(i) to .Value = 0

    
'   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

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now