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

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" wrote:

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

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?




"JLGWhiz" wrote:

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" wrote:

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

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson

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?



"Dave Peterson" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

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

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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.



"Dave Peterson" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

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.

art wrote:

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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.

art wrote:

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

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


art wrote:

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

"Dave Peterson" wrote:

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.

art wrote:

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Ambigiuos name Permission denied. Whats the problem?

"Dave Peterson" wrote:

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


art wrote:

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

"Dave Peterson" wrote:

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.

art wrote:

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Did you change the .listfillrange property?

How many Auto_Open procedures do you have?

art wrote:

Ambigiuos name Permission denied. Whats the problem?

"Dave Peterson" wrote:

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


art wrote:

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

"Dave Peterson" wrote:

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.

art wrote:

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" wrote:

=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.)

art wrote:

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" wrote:

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

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

art wrote:

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" wrote:

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 wrote:

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?

"JLGWhiz" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson