ComboBox AddItem Issue

[vijayendra.shukla]

Hi,

I am creating a dynamic UserForm in which i am creating a ComboBox. I want to populate the ComboBox dropdown with names of the sheets present in the workbook. UserForm is created successfully but ComboBox dropdown is always blank.
Here is the code:

Set NewComboBox = TempForm.Designer.Controls.Add("Forms.combobox.1")

    With NewComboBox
        .Name = "cboProductGroups"
        .Height = 15.75
        .Left = 225
        .Top = 24
        .Width = 126
        .BackColor = &H80000005
        .ForeColor = &H80000008
'        .Value = sProduct_Groups
'        .Text = sProduct_Groups
        .Enabled = True
    End With
 
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then NewComboBox.AddItem ws.Name
    Next ws
 
    NewComboBox.ListIndex = 1

As per the last line of the code, ComboBox is populated with the first index value of the drop-down, but when i try to click the drop down it is empty.
Let me know if I am missing anything here.

Thanks in advance.

 

[doofusboy]

Have you tried:

Set NewComboBox = TempForm.Designer.Controls.Add("Forms.combobox.1")

    With NewComboBox
        [B][COLOR=blue].Name = "cboProductGroups"
[/COLOR][/B]        .Height = 15.75
        .Left = 225
        .Top = 24
        .Width = 126
        .BackColor = &H80000005
        .ForeColor = &H80000008
'        .Value = sProduct_Groups
'        .Text = sProduct_Groups
        .Enabled = True
    End With
 
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then [COLOR=red][B]cboProductGroups[/B][/COLOR].AddItem ws.Name
    Next ws
 
[COLOR=red]    [B]cboProductGroups[/B][/COLOR].ListIndex = 1

Once you give the combobox a name [blue highlight], you need to refer to it by that name.

 

[RoryA]

To the best of my knowledge you cannot add a list to a combobox like that. Assuming you plan to show the form at once, you would need something like:

    Set frm = UserForms.Add(TempForm.Name)
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then frm.Controls("cboProductGroups").AddItem ws.Name
    Next ws
    frm.Show

 

[vijayendra.shukla]

Hi,

Thanks for your response. But when I am typing cboProductGroups. it does not show me auto-populated properties for this comboBox.
When i write NewComboBox., i am getting the properties of the comboBox auto-populated.
anyway I have Tried what you are saying but i am getting error 424 "Object Required" in the line
"cboProductGroups.AddItem ws.Name"

 

[vijayendra.shukla]

Hi rorya,

I have even tried your option but it is also not populating the drop-down for combobox.

anyway, for more details i am giving the initial part of my code here.

Sub MakeUserForm1()
    Dim TempForm As Object
    Dim NewLabel As MSForms.Label
    Dim NewComboBox As MSForms.ComboBox
    Dim NewButton As MSForms.commandbutton
    Dim NewCheckBox As MSForms.CheckBox
    Dim X As Integer
    Dim iNoOfSheets As Integer
    Dim iTotalRows As Integer
    Dim ws As Worksheet
    
    sProduct_Group = "Key Loans"
    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False
    
   Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
   
    'Create the User Form
    With TempForm
        .Properties("Caption") = "Details Of Product Group"
        .Properties("Width") = 504.75
        .Properties("Height") = 651
        .Properties("BackColor") = &H8000000A
    End With
    
    
    'Create Label Select the Product Group
    Set NewLabel = TempForm.Designer.Controls.Add("Forms.label.1")
    With NewLabel
        .Name = "Label1"
        .Caption = "Select the Product Group : "
        .Height = 18
        .Left = 108
        .Top = 24
        .Width = 120
        .Font.Name = "Arial"
        .Font.Size = 9
        .Font.Bold = True
        .BackColor = &H8000000A
        .ForeColor = &H80000012
    End With
    'Create ComboBox for Product Groups
    Set NewComboBox = TempForm.Designer.Controls.Add("Forms.combobox.1")
    With NewComboBox
        .Name = "cboProductGroups"
        .Height = 15.75
        .Left = 225
        .Top = 24
        .Width = 126
        .BackColor = &H80000005
        .ForeColor = &H80000008
        .Enabled = True
    End With
    
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then UserForms.Add(TempForm.Name).Controls("cboProductGroups").AddItem ws.Name
    Next ws
    VBA.UserForms.Add(TempForm.Name).Show
End Sub

let me know if i am missing anything here

To the best of my knowledge you cannot add a list to a combobox like that. Assuming you plan to show the form at once, you would need something like:

    Set frm = UserForms.Add(TempForm.Name)
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then frm.Controls("cboProductGroups").AddItem ws.Name
    Next ws
    frm.Show

 

[RoryA]

Your code is not in the same order as mine. You need to create an instance of the form, then populate the combobox. Not the other way round.

 

[vijayendra.shukla]

Thanks a ton Rorya. It worked.

Can you please explain me why do we have to create an instance specially for populating the drop-down for ComboBox?

If i create an instance of userform here, Do i have to work with that instance to create other things in my userform? Or rest of the code will work fine?

Thanks for your patience and time.

 

[RoryA]

A combobox has nowhere to store the list data unless you use a worksheet and set its rowsource property. So if you add the data at design time (which is what you were effectively doing) it is gone by the time you come to load the form. Once the form is loaded, the data will persist in memory until it is unloaded again. You should make any design changes first, then create an instance of the form, make any data changes, then show it.
Even better, don't create forms from scratch in code - it's a pain!

 

[vijayendra.shukla]

Well, I am newbie. Still learning VB.

Basically my requirement has forced me to go for dynamic user form.

In my case user will select drop-down value and based upon that a table of data will be presented to him/her. Now since for each drop-down value, corresponding datatable rows are varying therefore i can not have a static userform.

Can you point any useful link for dynamic UF creation? It would be of great help.

Thanks a lot indeed.

 

[RoryA]

My point was not to create the whole form from scratch. Just have a form with a combobox on it. You can alter all its properties at runtime, which is a lot easier than actually building the whole lot.