ComboBox Hell!!!

  • Thread starter Thread starter Dave Baranas
  • Start date Start date
D

Dave Baranas

Hi , I am having a terrible time getting a combobox to fill up now
that I have moved everything from a WorkSheet to a UserForm

When they were on a worksheet everything was fine as I could just use

ComBoBox.ListFillRange = ("A1:A10)

As long as my list was on the same sheet. I never got this to work
tryimg to load a list from another sheet ( and I tried the examples
from here but no luck, but I could live with that) So I changed my bad
design of lots of sheets to just a few and a master data sheet so I
could read and write from it...and life was good

So I try to go a step further and put all the controls on a nice user
form from a worksheet and spend hours because you can only drag and
drop them one at a time from a worksheet to a form and rename them
all. But thats OK too....cause I am going to be almost done I think

Now I can only fill a combobox by changing its properties in the form.
I tried these examples from an earlier post here and none of them
work. I have to show 2 columns in some and now this does not work
anymore because now the comboboxes are on a form instead of a
worksheet where I could get them to work either by changing object
properties or using VBA

Is there something so insanely stupid I am not seeing from lack of
sleep or can you really fill a combobox on a form using VBA code
instead of clicking on every one of these $%&^*(# things to get them
to fill up.

ThanksInAdvanceDaveBaranas

Using Excel 2002
-------------------------------------------------------------------------------------------------------------------

ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value

or

ComboBox1.RowSource = "Sheet1!A1:A10"

or

Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("A1:A10")
ComboBox1.AddItem cell.Value
Next cell

For more info on comboboxes/listboxes see
www.rubbershoe.com/listbox.htm
 
Dave Baranas said:
Hi , I am having a terrible time getting a combobox to fill up now
that I have moved everything from a WorkSheet to a UserForm

When they were on a worksheet everything was fine as I could just use

ComBoBox.ListFillRange = ("A1:A10)

As long as my list was on the same sheet. I never got this to work
tryimg to load a list from another sheet ( and I tried the examples
from here but no luck, but I could live with that) So I changed my bad
design of lots of sheets to just a few and a master data sheet so I
could read and write from it...and life was good

So I try to go a step further and put all the controls on a nice user
form from a worksheet and spend hours because you can only drag and
drop them one at a time from a worksheet to a form and rename them
all. But thats OK too....cause I am going to be almost done I think

Now I can only fill a combobox by changing its properties in the form.
I tried these examples from an earlier post here and none of them
work. I have to show 2 columns in some and now this does not work
anymore because now the comboboxes are on a form instead of a
worksheet where I could get them to work either by changing object
properties or using VBA

Is there something so insanely stupid I am not seeing from lack of
sleep or can you really fill a combobox on a form using VBA code
instead of clicking on every one of these $%&^*(# things to get them
to fill up.
Click to expand...

Sure suppose you want to use the values in column 1 rows 1 to 8

Dim Rownum As Long, n As Long

Rownum = 8
ComboBox1.Clear

For n = 1 To Rownum
With ActiveSheet
ComboBox1.AddItem .Cells(n, 1)
End With
Next n

ComboBox1.ListIndex = 0


Keith
 
'pls try this
'Create sheet name = mySheet
'insert this code to userform with ComboBox1
Private Sub UserForm_Initialize()

On Error GoTo error_hen
'Worksheets("mySheet").Select
With Worksheets("mySheet").Range("A:A") 'attention! name
of the sheet, range
Set c = .Find(What:="*", LookIn:=xlValue) ' "*" stands
for every not empty
If Not c Is Nothing Then
firstAddress = c.Address

Do
Me.ComboBox1.AddItem c.Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <>
firstAddress

End If
End With
Exit Sub
error_hen:

MsgBox "Error !"

End Sub
'##############################
'mailto:R_K_Gajda@ poczta.fm
 
The simple code you show should work:

ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value

or

ComboBox1.RowSource = "Sheet1!A1:A10"

or

Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("A1:A10")
ComboBox1.AddItem cell.Value
Next cell

You should put one of these approaches in the initialize event of the
userform. By the way, copying the controls from a worksheet to a userform
doesn't seem necessary. You can just use the controls from the control
toolbox and place them on the form in the VBE - this takes much less time,
even if you have to change a few properties.

When specifing the listfillrange or the rowsource, include the sheet name:
Sheet1!A1:A10 this avoids the problems you were having. For a
multicolumn combobox or listbox, set the columncount property.
 
Yes putting this into the initialize event worked.

Thanks everyone!


The simple code you show should work:

ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value

or

ComboBox1.RowSource = "Sheet1!A1:A10"

or

Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("A1:A10")
ComboBox1.AddItem cell.Value
Next cell

You should put one of these approaches in the initialize event of the
userform. By the way, copying the controls from a worksheet to a userform
doesn't seem necessary. You can just use the controls from the control
toolbox and place them on the form in the VBE - this takes much less time,
even if you have to change a few properties.
Click to expand...

Its just that I had all my controls on sheets and then wanted to get
them off the sheets and onto a form . I had over 100 of them so it was
a pain to drag them over 1 at a time. looking back at least I know
never to do that again!!
 
Back
Top