Combobox Empty

[Vampy99]

Hi guys,

As previously mentioned, I'm a complete beginner with VB.

I have a form that pop's up when you click a button, please see the form below;

I can't get my info to populate the combobox's, please see my code below;

Private Sub UserForm1_Initialize()

UserForm1.Activate

comboday.Enabled
combomonth.Enabled
comboyear.Enabled


With comboday.Text

.AddItem "01"
.AddItem "02"
.AddItem "03"
.AddItem "04"
.AddItem "05"
.AddItem "06"
.AddItem "07"
.AddItem "08"
.AddItem "09"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"

End With

With combomonth

.AddItem "01"
.AddItem "02"
.AddItem "03"
.AddItem "04"
.AddItem "05"
.AddItem "06"
.AddItem "07"
.AddItem "08"
.AddItem "09"
.AddItem "10"
.AddItem "11"
.AddItem "12"

End With

With comboyear


.AddItem "2011"
.AddItem "2012"
.AddItem "2013"
.AddItem "2014"

End With

End Sub

The bits in bold are things I've added in to try to get them to work.

Can anyone help me?

Thanks,

Luke

 

[Trevor G]

Luke Hi,

Comment out the following, not need to use the userform1.activate if you have added the code to the userform1.initialize

'UserForm1.Activate

'comboday.Enabled
'combomonth.Enabled
'comboyear.Enabled

with the with statement just refer to the combo no need to add the text

With comboday '.Text

 

[Trevor G]

Also look to use a sheet to hold the data, so you can manage it easily rahter than have to come back to the code so look here

Excel Workbook
	A	B	C
1	Day	Month	Year
2	1	1	2011
3	2	2	2012
4	3	3	2013
5	4	4	2014
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	10	10
12	11	11
13	12	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26	25
27	26
28	27
29	28
30	29
31	30
32	31
Sheet2

Then the code can refer to the sheet and cells.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>Worksheets("Sheet2").Select<br>Range("A2").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>Me.cboDay.AddItem ActiveCell.Value<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br>Range("b2").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>Me.cboMonth.AddItem ActiveCell.Value<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br>Range("C2").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>Me.cboYear.AddItem ActiveCell.Value<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Vampy99]

Thanks for the response Trevor. I managed to get your method to work but preferred my own method, it turned out it was a problem with my Excel, I had to create the form before the button otherwise the combobox's would remain empty, weird I know.

Sorted now though

Thanks,

Luke

 

[Norie]

Luke

The problem with your code was probably because you used UserForm1_Initialize.

You don't use the actual name of the form, UserForm1 will do.