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

 

[dmt32]

its not working because you have made the classic error of renaming the event to match your userform name

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

You should NOT rename any of the events - Its UserForm regardless of your forms name

Private Sub UserForm_Initialize()

End Sub

Dave

 

[Mel Smith]

Hello Dave,

I've removed the "1" from the initialise statement but when i click on the command button (from the worksheet) the userform does not show. Do i need to remove the "1" fro mevrywhere?

Mel

Private Sub UserForm_Click()
UserForm1.Show
End Sub

Private Sub UserForm_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"
End With
With ComboBox2.List = Sheets(1).Range("A4, A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26, A28, A30").Value
End With
With ComboBox3.List = Sheets(9).Range("B5:B64").Value
End With
With ComboBox4
.AddItem "First"
.AddItem "Second"
End With

End Sub

 

[Mel Smith]

I've added some more code but still no luck with displaying the UserForm. The following is displayed on 'debug':

Private Sub CommandButton1_Click()
UserForm1.Show "this line is highlighted"
End Sub

This is the code so far from the UF within VBA:

Private Sub UserForm_Click()
UserForm1.Show
End Sub

Private Sub UserForm_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"
End With
With ComboBox2.List = Sheets(1).Range("A4, A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26, A28, A30").Value
End With
With ComboBox3.List = Sheets(9).Range("B5:B64").Value
End With
With ComboBox4
.AddItem "First"
.AddItem "Second"
End With

End Sub
Private Sub ComboBox1_Change()
Dim ans As String
ans = ComboBox1.Value
Dim ant As String
ant = ComboBox2.Value
Dim anu As String
anu = ComboBox3.Value
Dim anv As String
anv = ComboBox4.Value
End Sub

Mel

 

[dmt32]

Made some correction to your forms event code as cannot use List property with non contiguous ranges in manner you are using
Code must be placed in your forms code page

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")
        ComboBox2.AddItem cell.Value
    Next
  
    ComboBox3.List = Sheets(9).Range("B5:B64").Value
  
    With ComboBox4
    .AddItem "First"
    .AddItem "Second"
    End With

End Sub

Form opened & controls populated ok for me - where are you calling it from?

also,
delete this code

Private Sub UserForm_Click()
UserForm1.Show
End Sub

Dave

 

[Mel Smith]

hi dave,

i made all the changes, thank you. However, I am still getting the UF not showing. I am trying to call it from the Worksheet via a CommandButton and when in Design Mode the "code" shows: =EMBED("Forms.CommandButton.1",""). when I click on it this is displayed in VB:

Private Sub CommandButton1_Click()
UserForm1.Show "this line is highlighted"
End Sub

 

[Fluff]

In the VB Editor select Tools > Options > General > & check "Break in class module" >Ok
Then try clicking the button again, it should take you the line that is causing the problem.

 

[dmt32]

Fluff beat me to it!

Dave

 

[Mel Smith]

Done that. Unfortunately the same result

 

[dmt32]

Done that. Unfortunately the same result

Have you come out of design mode?

If still having issues, publish all your forms code