combobox list range

[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

 

[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

 

[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?

 

[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

 

[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?

 

[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

 

[Dave Peterson]

Maybe you could drop the code altogether and use a dynamic range name.

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

 

[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

 

[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

 

[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.

 

[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".

 

[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".

 

[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".

 

[art]

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

 

[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?

 

[art]

Ambigiuos name Permission denied. Whats the problem?

 

[Dave Peterson]

Did you change the .listfillrange property?

How many Auto_Open procedures do you have?

Ambigiuos name Permission denied. Whats the problem?