Missing code to populate ComboBoxes in VBA

[Mel Smith]

I have the following code in my UserForm but when I click on the DropDown from within the UserForm on my worksheet, all Comboboxes are blank. What have I done wrong or am I missing some lines of code?

Private Sub UserForm1_Initialize()
With ComboBox1
.AddItem "County Championship"
.AddItem "Royal London One Day Cup"
.AddItem "Vitality T20 Blast"
.AddItem "Second XI Championship"
.AddItem "Second XI 50 Over Friendly"
.AddItem "Second XI T20 (SET20)"
.AddItem "Second XI Friendlies"

With ComboBox2.List = Sheets(1).Range("A4,A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26, A28, A30").Value

With ComboBox3.List = Sheets(9).Range("B5:B64").Value

With ComboBox4
.AddItem "First"
.AddItem "Second"
End Sub

mamy thanks,

Mel

 

[Mel Smith]

Definitely out of Design Mode. Total VB code:

and:

Private Sub UserForm_Initialize()

Dim cell As Range

With ComboBox1
.AddItem "County Championship"
.AddItem "Royal London One Day Cup"
.AddItem "Vitality T20 Blast"
.AddItem "Second XI Championship"
.AddItem "Second XI 50 Over Friendly"
.AddItem "Second XI T20 (SET20)"
.AddItem "Second XI Friendlies"
End With


For Each cell In Sheets(1).Range("A4, A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26, A28, A30").Value
ComboBox2.AddItem cell.Value
Next
ComboBox3.List = Sheets(9).Range("B5:B64").Value

With ComboBox4
.AddItem "First"
.AddItem "Second"
End With

End Sub

Mel

 

[dmt32]

YOU have added some code to my update

For Each cell In Sheets(1).Range("A4, A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26, A28, A30").Value
ComboBox2.AddItem cell.Value
Next

DELETE code shown in BOLD & hopefully, problem solved

Dave

 

[Mel Smith]

Perfect. Thank you so much.

I've a long way to go and much to learn! I shall now try to figure out how to deal with the rest of this form which has 25 text boxes to "submit" data to up to 60 worksheets.

Mel

 

[dmt32]

Perfect. Thank you so much.

I've a long way to go and much to learn! I shall now try to figure out how to deal with the rest of this form which has 25 text boxes to "submit" data to up to 60 worksheets.

Mel

Your welcome but take this lesson away with you - DO NOT make changes to code offered to you before to you tried them out.

A suggestion for your textboxes to range would be if they are all using their default names "TextBox1" TextBox2" etc
would be to use an array - something like following

Private Sub CommandButton1_Click()
    Dim arr(1 To 25) As Variant
    Dim NextRow As Long
    Dim i As Integer
    
    For i = 1 To 25
        arr(i) = Me.Controls("TextBox" & i).Value
    Next i
    
     With Worksheets("Sheet1")
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(NextRow, 1).Resize(, UBound(arr)).Value = arr
     End With
End Sub

Dave

 

[Mel Smith]

Many thanks, Dave. No doubt I shall be able to use some of this but, as you might expect, the 'submitting' of the data is not going to be anything like straightforward! If you like a challenge I'll be happy to share the whole problem with you.

Mel

 

[dmt32]

not a solution, just to give you an idea how you could approach it - do what you can & if get stuck, post to the board, plenty here to help you.

Dave

 

[Mel Smith]

Oh, I shall! thanks.
Mel