Adding WorksheetData to a ComboBox on a form?

[jsmarsha]

Hi Everyone,

I am a real novice and am having a difficult time with coding. I
agreed to make an Excel program to track adverse drug experiences for
work and I would like to fill a ComboBox on a form I created with data
from a single column on a worksheet.

Here is the coding for my form:

Private Sub ComboBox1_Change()
Dim i As Long
Dim S1 As Worksheet

Set S1 = Sheets("DataEntry")

S1.Select
Range("A2").Select
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
UserForm6.ComboBox1.AddItem S1.Cells(i, 1).Value
Next i
End Sub


Is there something goofy with my coding?! It seems to run alright, but
there are no values listed in the drop-down box. PLEASE HELP!!! I've
wasted too much time with something that is probably pretty easy to
solve.

Thanks,
Jeff

 

[Rick Hansen]

Hi Jeff,
You nearly had your code right, it just needs a few changes. Plus this
code should be run in the UserForm_Initialize() event procedure. See the
code below. Enjoy....

Rick


Private Sub UserForm_Initialize()
Dim i As Long
Dim S1 As Worksheet

Set S1 = Worksheets("DataEntry")

' S1.Select (not required)
' Range("A2").Select (not required)
' For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row (see next line)

For i = 2 To S1.Range("A2").End(xlDown).Row
UserForm6.ComboBox1.AddItem S1.Cells(i, 1).Value
Next i

End Sub

 

[Leith Ross]

Hello Jeff,

To start with, you have your code in the wrong event. It should b
placed in the Private Sub UserForm_Activate() event module.

_______________________________

Private Sub UserForm_Activate()
Dim i As Long
Dim S1 As Worksheet

Set S1 = Sheets("DataEntry")

For i = 2 To S1.Cells(Rows.Count, 1).End(xlUp).Row
UserForm6.ComboBox1.AddItem S1.Cells(i, 1).Value
Next i
End Sub
_______________________________

Sincerely,
Leith Ros

 

[jsmarsha]

Hey Rick,

Thank you very much for your help! I was spinning my wheels and going
crazy...

Take care,
Jeff

 

[jsmarsha]

Hi Leith,

Thank you very much for responding so quickly! I was so frustrated
with not being able continue my coding. Very interesting that there
were two solutions to my problem.

Take care,
Jeff

 

[Guest]

Hi Jeff,
You nearly had your code right, it just needs a few changes. Plus this
code should be run in the UserForm_Initialize() event procedure. See the
code below. Enjoy....

Rick


Private Sub UserForm_Initialize()
Dim i As Long
Dim S1 As Worksheet

Set S1 = Worksheets("DataEntry")

' S1.Select (not required)
' Range("A2").Select (not required)
' For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row (see next line)

For i = 2 To S1.Range("A2").End(xlDown).Row
UserForm6.ComboBox1.AddItem S1.Cells(i, 1).Value
Next i

End Sub

 

[Guest]

Hi Rick,

I was just reading your reply to Jeff re. populating a combox from a single
column on a worksheet. I have been trying to adapt this so that i can
populate two comboboxes on the same user form from two separate columns on
the same worksheet. I am having no luck!!!

Would appreciate if you could advise.

Thanks,

Ciara