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