Populating Dynamic combobox

Question

I have a number of controls that are added during runtime but I want to populate a combobox that is created during runtime based on answers received from the the other dynamic controls (its hard to word)!

I will try and explain via my code! During runtime the following controls are added to my userform:

Set cLabel = Me.Controls.Add("Forms.Label.1")
With cLabel
    .Caption = "Do you study maths?"
    .Font.Size = 8
    .Font.Bold = False
    .Font.Name = "Tahoma"
    .Left = 38
    .Top = mathslbl
    .Width = 220
End With

Set cManagedOptionButtonYes= Me.Controls.Add("Forms.OptionButton.1")
With cManagedOptionButtonYes
    .Caption = "Yes"
    .Name = "fibreRingYes" & i
    .GroupName = "fibreRing" & i
    .Left = 160
    .Top = mathsYes
    .Width = 100
    .Height = 15.75
End With

ReDim Preserve TextListBox(1 To i)
Set TextListBox(i).mathsGroupYes = cManagedOptionButtonYes

Set cStudentDropdown = Me.Controls.Add("Forms.Combobox.1")
With cStudentDropdown 
    .Name = "StudenDropdown1" & (i)
    .Left = 50
    .Top = studentCombobox
    .Width = 130
    .Height = 18
 End With

 ReDim Preserve TextListBox(1 To i)
 Set TextListBox(i).studentdropdown1Group = cStudentDropdown

So during runtime the following controls are created, these controls ask whether the student studies maths or not and dependant on the answer the user gives the combobox will be populated. Below is the code that is trying to populate the combobox based on the users answer to the question:

Private Sub cManagedOptionButtonYes_Click()
    Dim rng As Range
    Dim cell As Range
    Dim c As Range

    With cStudentDropdown 
        Sheets("Students").Activate
        For Each c In Sheets("Students").Range("C14:C86")
             .AddItem c.Value
        Next
    End With
End Sub

When the code tries to execute i get the following error Object variable or with block variable not set and I can't figure out why this won't work for me!

In the meantime, you may want to see [THIS](http://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically-using-vba-on-excel) — Siddharth Rout, Oct 09 '14 at 10:40
In the immediate window check what is the`Type` of `cStudentDropdown` — Siddharth Rout, Oct 09 '14 at 12:10
Where is `cStudentDropdown` declared and initialised? I suspect it has gone out of scope. — Rory, Oct 09 '14 at 12:16
its a control, it is declared at the top of the page `Dim WithEvents cManualDropdown As MSForms.ComboBox` — user3538102, Oct 09 '14 at 12:37
Actually thats just setting it up so it can handle events, it is declared inside a class instead of outside of them all! maybe that is my issue, but surely if that was the problem I wouldnt get as far as the line where the code breaks — user3538102, Oct 09 '14 at 12:38

score 0 · Answer 1 · answered Oct 10 '14 at 15:48

assuming this is in the userform's code page:

Private Sub cManagedOptionButtonYes_Click()
'Dim rng As Range
'Dim cell As Range
Dim c As Range

With Me.cStudentDropdown   'Me. is not necessary, but i like to use it in order to have a dropdown menu with all functions and controls created on the form
    'Sheets("Students").Activate
    .clear
    For Each c In Sheets("Students").Range("C14:C86")
         '.AddItem c.Value 'should work (whatever its format i think)..., so try this :
         'make sure no cell in the looping range returns a error result such as #N/A something like that
         .additem
         .list(.listcount-1) = c.value 'or try c.value2
    Next
End With
End Sub

`

Populating Dynamic combobox

1 Answers1