combobox list range

A

art

Hello:

How can I set a combo list to a range in a certain sheet that the length of
the range keeps changing and new names are added to the list?

For e.g. the list starts off from I2:I10, however I keep adding to the list
and I want the combobox to add the newer ones to the list as well. I started
using ComboBox1.ListFillRange = MyRange, which is I2:last cell in this column?

Any help?

Thanks.

Art
 
J

JLGWhiz

This would be incorporated into the UserForm initialize
event code.

Dim sh As Worksheet, lstRw As Long
sh = ActiveSheet
lstRw = sh.Cells(Rows.Coount, 9).End(xlUp).Row
srcRng = Range("I2:I & lstRw).Address
Me.ComboBox1.ListFillRange = srcRng
 
A

art

It's a active x combo box embdded in the sheet, however I tried it and syntax
error comes up, with srcRng = Range("I2:I & lstRw).Address highlighted?
 
D

Dave Peterson

There were a couple of typos in the original code:

Option Explicit
Private Sub UserForm_Initialize()

Dim sh As Worksheet
Dim lstRw As Long
Dim srcRng As String

Set sh = ActiveSheet
With sh
lstRw = .Cells(.Rows.Count, 9).End(xlUp).Row
End With
srcRng = Range("I2:I" & lstRw).Address(external:=True)
Me.ComboBox1.RowSource = srcRng

End Sub
 
A

art

I need it for an activex combo box. it doesn't work for that. How can I make
it work for the activeX combobox as well?
 
A

art

I think I got it strieght a little I changed it to listfillrange. However the
problem is that the column I has a formula, so It goes down. And most
importantly, The combo list is long but I don't see and thing in the list. It
is empty. Here is the formula that I adjusted:

Dim sh As Worksheet
Dim lstRw As Long
Dim srcRng As String
Sheets("Customer List").Select
Set sh = ActiveSheet
With sh
lstRw = .Cells(.Rows.Count, 9).End(xlUp).Row
End With
srcRng = Range("I2:I" & lstRw).Address(external:=True)
ComboBox1.ListFillRange = srcRng
 
A

art

First of all I can't use it because there is a problem with this:

"The ListFillRange property of an ActiveX control does not update properly
when rows or columns in the ListFillRange are inserted or deleted"
(http://support.microsoft.com/kb/160206)

Besides, I don't know why, but it does not work, The combobox list is
nothing (empty).

I am trying to use WorksheetFunction.CountA(Range("I:I")) but I don't know
why is the result 2000 (which 2000 has formulas in them) while only 10 are
not empty (only ten cells are filled with info). Please help.

Thanks
 
D

Dave Peterson

=counta() will return the number of cells that aren't empty. If a cell contains
a formula, then it's not empty--no matter if it evaluates to "" or anything
else.

Maybe you could loop through the range and populate the combobox with the values
using .additem:

Option Explicit
Private Sub UserForm_Initialize()

Dim sh As Worksheet
dim myRng as range
dim myCell as range

Set sh = ActiveSheet

With sh
set myrng = .range("I2",.cells(.Rows.Count, "i").End(xlUp))
End With

for each mycell in myrng.cells
if mycell.value = "" then
'skip it
else
me.combobox1.additem mycell.value
end if
next mycell

End Sub

(Untested, uncompiled.)
First of all I can't use it because there is a problem with this:

"The ListFillRange property of an ActiveX control does not update properly
when rows or columns in the ListFillRange are inserted or deleted"
(http://support.microsoft.com/kb/160206)

Besides, I don't know why, but it does not work, The combobox list is
nothing (empty).

I am trying to use WorksheetFunction.CountA(Range("I:I")) but I don't know
why is the result 2000 (which 2000 has formulas in them) while only 10 are
not empty (only ten cells are filled with info). Please help.

Thanks
 
A

art

Is there now code to find the last cell that has an actual value in it? Once
I have that I think I have it figuered all out.
 
A

art

I trid your loop, however as I posted in the begining I'm using an activex
Combo box not a userform. Also it turns out an error "permission denied".
 
A

art

I trid your loop, however as I posted in the begining I'm using an activex
Combo box not a userform. Also it turns out an error "permission denied".
 
D

Dave Peterson

First, get rid of that .listfillrange property. Just clear out anything you
typed.

Second, I'm not sure when the combobox should be initialized.

Maybe you can use the Auto_open procedure -- when excel opens the workbook:

Option Explicit
Sub Auto_Open()

Dim sh As Object
Dim myRng As Range
Dim myCell As Range

Set sh = Worksheets("sheet1")

With sh
Set myRng = .Range("I2", .Cells(.Rows.Count, "i").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'skip it
Else
sh.ComboBox1.AddItem myCell.Value
End If
Next myCell

End Sub


I would use a specific sheet and not depend on the activesheet.
I trid your loop, however as I posted in the begining I'm using an activex
Combo box not a userform. Also it turns out an error "permission denied".
 
D

Dave Peterson

Option Explicit
Sub Auto_Open()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet2")
Set myRng = .Range("I2", .Cells(.Rows.Count, "i").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'skip it
Else
Worksheets("sheet1").ComboBox1.AddItem myCell.Value
End If
Next myCell

End Sub

The combo box is one sheet1 and the list on sheet 2?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top