Hi All

not my code, but I would love to fix it. The rows.value(i+1,1) --
statements are wrong.

How is it fixed please.

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Rows.Value(i + 1, 1) = "" Then Exit For
list(i).text = Trim(orange.Rows.Value(i + 1) & " (" &
orange.Rows.Value(i + 1) & ")")
list(i).id = orange.Rows.Value(i + 1)
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

Stan

You haven't told us exactly what you're trying to achieve, which makes it
harder for us to help. I gather that you have a named range (Proj_range) and
you want the values in that range to be added to a list box (Listbox1). What
is not clear is if that range is one column or more than one column, or if
you want the existing listbox added to or replaced with the new list. Also
there's apparently redundant code that I'm not sure if it's supposed to be
doing something or if it's leftovers from attempts to get it working.

---------------------------
Firstly, answering your question of why rows.value(i+1,1) doesn't work. It's
because the (i+1,1) parameter should be part of the range object, not part of
the .Value property.

In your for loop, i+1 represents a row number.
(i+1,1) represents the first cell of a row.
orange represents a range.
orange.Rows(i+1) will represent a particular row in the range (and if the
range is only 1 column wide this will be a single cell).
orange.Cells(i+1,1) will represent a particular cell in the range
orange.Cells(i+1,1).Value will represent the value (contents) of a cell in
the range.

So everywhere where you have:
orange.Rows.Value(i + 1, 1)
I think it should instead be:
orange.Cells(i+1,1).Value

And everywhere where you have:
orange.Rows.Value(i + 1)
I think it should instead be:
orange.Rows(i+1).Value
except in this case you're trying to get the "value" of a whole row of
orange, which will probably only work if orange is only one column wide. If
it's more than one column wide, and you want the value in the first column,
then instead use:
orange.Cells(i+1,1).Value

---------------------------
Secondly, you've used list as a variable name. List is a VBA keyword, so
it's a bad idea to try to use it as a variable name. So wherever you use
"list" in your code change it to something else - "arrList" for example.

Tip - to test to see if a variable name is "safe" to use, in the MS Visual
Basic Editor type the word on it's own in lower case on a line and hit enter.
If it stays lower case, then the editor has not recognised it as a special
word and its probably safe to use. If the editor does recognise the word, it
will change its case, usually so that it starts with an uppercase letter. If
you try that with list, the editor will turn it into List, which gives you a
clue that it's not a good variable name.

---------------------------
Thirdly, some suggestions for changing parts of your code.

In your first for...next loop you step from zero to the number of rows in
the range. Starting at zero instead of 1 makes some sense because you use i
in the array reference - and an array starts with element 0. However, because
you start at zero not 1 you need to stop at "orange.Rows.Count - 1",
otherwise you will have one more step than there are rows in your range. So
change the line to:
For i = 0 To orange.Rows.Count - 1

Inside your Trim function, if you re-arrange it as I suggest above, it will
only work if orange is a single column wide ... but if it is only one column
wide, then you're concatenating the same value to itself (which seems odd to
me). If the cell had a value of "pluto" then you would end up with
pluto(pluto)
in your list. Is that what you want? Is the bit inside the brackets supposed
to be the value in the second column? Also, the Trim would make more sense
around each range, rather than around the concatenation of the 2 ranges.
Try this instead for that line of code (all on one line, not wrapped like
this):
arrList(i).text = Trim(orange.Cells(i+1,1).Value) & " (" &
Trim(orange.Cells(i+1,2).Value) & ")"
That assumes you want the second column in the brackets. If you want the
first column (or if there is only one column), change the 2 to a 1. Also note
I didn't use your variable name of "list", as per my comment above.

Then you try to add an item to the list box that uses the variable orow...
but you haven't set orow to anything yet... and it looks like you're trying
to do the same as list(i).text... and later in the macro you add list(i).text
to the listbox... so I think the line:
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
can be deleted.

You have a line:
sort list
I don't know if an array can be sorted easily, but I'm certain that line
won't do it. For the time being, get rid of that line and see if you can get
it working without sorting the list. Deal with the sorting later.

You then have a little for..next loop to add the array items to the listbox
one at a time. That should work, but it won't replace any existing items in
the list (it will keep adding on to the existing list).
Instead, you can allocate the whole array to the list box at once:
Me.ListBox1.ControlFormat.List = arrList
(use that in place of the whole for-next loop, not just the line inside it)


"Stan" wrote:

Hi All

not my code, but I would love to fix it. The rows.value(i+1,1) --
statements are wrong.

How is it fixed please.

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Rows.Value(i + 1, 1) = "" Then Exit For
list(i).text = Trim(orange.Rows.Value(i + 1) & " (" &
orange.Rows.Value(i + 1) & ")")
list(i).id = orange.Rows.Value(i + 1)
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

Stan

"Cringing Dragon"

fantastic help and almost there, and I am sorry I forgot to give you the
range information.

The range has two columns and is acting as a look up, the first column ID
contains a code the second the description. Range is on a hidden sheet, and
its a timesheet system, you click on a button and the user form list box is
mention to open and you can then scroll down and select the project you are
working one.

As I have said not my code, but I have learned a lot more from your kind
help. If I comment out the line below, I get a list box user form with two
identical items say 203 (203), on selecting this I get 203 (in ID) and the
Description in the correct box, so alls I need to now is why the line below
deos not work, please.


Below is the latest code, which fails on

Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"

As you say its not set. The rest works fine, but not the expect outcome as
above.

list is not a key word so I get away with it.

Stan

New code

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Cells(i + 1, 1).Value = "" Then Exit For
list(i).text = Trim(orange.Cells(i + 1, 1).Value) & " (" &
Trim(orange.Cells(i + 1, 1).Value) & ")"
list(i).id = orange.Cells(i + 1, 1).Value
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

"Cringing Dragon" wrote:

You haven't told us exactly what you're trying to achieve, which makes it
harder for us to help. I gather that you have a named range (Proj_range) and
you want the values in that range to be added to a list box (Listbox1). What
is not clear is if that range is one column or more than one column, or if
you want the existing listbox added to or replaced with the new list. Also
there's apparently redundant code that I'm not sure if it's supposed to be
doing something or if it's leftovers from attempts to get it working.

---------------------------
Firstly, answering your question of why rows.value(i+1,1) doesn't work. It's
because the (i+1,1) parameter should be part of the range object, not part of
the .Value property.

In your for loop, i+1 represents a row number.
(i+1,1) represents the first cell of a row.
orange represents a range.
orange.Rows(i+1) will represent a particular row in the range (and if the
range is only 1 column wide this will be a single cell).
orange.Cells(i+1,1) will represent a particular cell in the range
orange.Cells(i+1,1).Value will represent the value (contents) of a cell in
the range.

So everywhere where you have:
orange.Rows.Value(i + 1, 1)
I think it should instead be:
orange.Cells(i+1,1).Value

And everywhere where you have:
orange.Rows.Value(i + 1)
I think it should instead be:
orange.Rows(i+1).Value
except in this case you're trying to get the "value" of a whole row of
orange, which will probably only work if orange is only one column wide. If
it's more than one column wide, and you want the value in the first column,
then instead use:
orange.Cells(i+1,1).Value

---------------------------
Secondly, you've used list as a variable name. List is a VBA keyword, so
it's a bad idea to try to use it as a variable name. So wherever you use
"list" in your code change it to something else - "arrList" for example.

Tip - to test to see if a variable name is "safe" to use, in the MS Visual
Basic Editor type the word on it's own in lower case on a line and hit enter.
If it stays lower case, then the editor has not recognised it as a special
word and its probably safe to use. If the editor does recognise the word, it
will change its case, usually so that it starts with an uppercase letter. If
you try that with list, the editor will turn it into List, which gives you a
clue that it's not a good variable name.

---------------------------
Thirdly, some suggestions for changing parts of your code.

In your first for...next loop you step from zero to the number of rows in
the range. Starting at zero instead of 1 makes some sense because you use i
in the array reference - and an array starts with element 0. However, because
you start at zero not 1 you need to stop at "orange.Rows.Count - 1",
otherwise you will have one more step than there are rows in your range. So
change the line to:
For i = 0 To orange.Rows.Count - 1

Inside your Trim function, if you re-arrange it as I suggest above, it will
only work if orange is a single column wide ... but if it is only one column
wide, then you're concatenating the same value to itself (which seems odd to
me). If the cell had a value of "pluto" then you would end up with
pluto(pluto)
in your list. Is that what you want? Is the bit inside the brackets supposed
to be the value in the second column? Also, the Trim would make more sense
around each range, rather than around the concatenation of the 2 ranges.
Try this instead for that line of code (all on one line, not wrapped like
this):
arrList(i).text = Trim(orange.Cells(i+1,1).Value) & " (" &
Trim(orange.Cells(i+1,2).Value) & ")"
That assumes you want the second column in the brackets. If you want the
first column (or if there is only one column), change the 2 to a 1. Also note
I didn't use your variable name of "list", as per my comment above.

Then you try to add an item to the list box that uses the variable orow...
but you haven't set orow to anything yet... and it looks like you're trying
to do the same as list(i).text... and later in the macro you add list(i).text
to the listbox... so I think the line:
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
can be deleted.

You have a line:
sort list
I don't know if an array can be sorted easily, but I'm certain that line
won't do it. For the time being, get rid of that line and see if you can get
it working without sorting the list. Deal with the sorting later.

You then have a little for..next loop to add the array items to the listbox
one at a time. That should work, but it won't replace any existing items in
the list (it will keep adding on to the existing list).
Instead, you can allocate the whole array to the list box at once:
Me.ListBox1.ControlFormat.List = arrList
(use that in place of the whole for-next loop, not just the line inside it)


"Stan" wrote:

Hi All

not my code, but I would love to fix it. The rows.value(i+1,1) --
statements are wrong.

How is it fixed please.

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Rows.Value(i + 1, 1) = "" Then Exit For
list(i).text = Trim(orange.Rows.Value(i + 1) & " (" &
orange.Rows.Value(i + 1) & ")")
list(i).id = orange.Rows.Value(i + 1)
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

Stan

Hi Here is the working code, I quantum leaped (Stan = Diskdr)

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Cells(i + 1, 1).Value = "" Then Exit For
list(i).text = Trim(orange.Cells(i + 1, 1).Value) & " " &
Trim(orange.Cells(i + 1, 2).Value)
list(i).id = orange.Cells(i + 1, 1).Value
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

"Diskdr" wrote:

"Cringing Dragon"

fantastic help and almost there, and I am sorry I forgot to give you the
range information.

The range has two columns and is acting as a look up, the first column ID
contains a code the second the description. Range is on a hidden sheet, and
its a timesheet system, you click on a button and the user form list box is
mention to open and you can then scroll down and select the project you are
working one.

As I have said not my code, but I have learned a lot more from your kind
help. If I comment out the line below, I get a list box user form with two
identical items say 203 (203), on selecting this I get 203 (in ID) and the
Description in the correct box, so alls I need to now is why the line below
deos not work, please.


Below is the latest code, which fails on

Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"

As you say its not set. The rest works fine, but not the expect outcome as
above.

list is not a key word so I get away with it.

Stan

New code

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Cells(i + 1, 1).Value = "" Then Exit For
list(i).text = Trim(orange.Cells(i + 1, 1).Value) & " (" &
Trim(orange.Cells(i + 1, 1).Value) & ")"
list(i).id = orange.Cells(i + 1, 1).Value
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

"Cringing Dragon" wrote:

You haven't told us exactly what you're trying to achieve, which makes it
harder for us to help. I gather that you have a named range (Proj_range) and
you want the values in that range to be added to a list box (Listbox1). What
is not clear is if that range is one column or more than one column, or if
you want the existing listbox added to or replaced with the new list. Also
there's apparently redundant code that I'm not sure if it's supposed to be
doing something or if it's leftovers from attempts to get it working.

---------------------------
Firstly, answering your question of why rows.value(i+1,1) doesn't work. It's
because the (i+1,1) parameter should be part of the range object, not part of
the .Value property.

In your for loop, i+1 represents a row number.
(i+1,1) represents the first cell of a row.
orange represents a range.
orange.Rows(i+1) will represent a particular row in the range (and if the
range is only 1 column wide this will be a single cell).
orange.Cells(i+1,1) will represent a particular cell in the range
orange.Cells(i+1,1).Value will represent the value (contents) of a cell in
the range.

So everywhere where you have:
orange.Rows.Value(i + 1, 1)
I think it should instead be:
orange.Cells(i+1,1).Value

And everywhere where you have:
orange.Rows.Value(i + 1)
I think it should instead be:
orange.Rows(i+1).Value
except in this case you're trying to get the "value" of a whole row of
orange, which will probably only work if orange is only one column wide. If
it's more than one column wide, and you want the value in the first column,
then instead use:
orange.Cells(i+1,1).Value

---------------------------
Secondly, you've used list as a variable name. List is a VBA keyword, so
it's a bad idea to try to use it as a variable name. So wherever you use
"list" in your code change it to something else - "arrList" for example.

Tip - to test to see if a variable name is "safe" to use, in the MS Visual
Basic Editor type the word on it's own in lower case on a line and hit enter.
If it stays lower case, then the editor has not recognised it as a special
word and its probably safe to use. If the editor does recognise the word, it
will change its case, usually so that it starts with an uppercase letter. If
you try that with list, the editor will turn it into List, which gives you a
clue that it's not a good variable name.

---------------------------
Thirdly, some suggestions for changing parts of your code.

In your first for...next loop you step from zero to the number of rows in
the range. Starting at zero instead of 1 makes some sense because you use i
in the array reference - and an array starts with element 0. However, because
you start at zero not 1 you need to stop at "orange.Rows.Count - 1",
otherwise you will have one more step than there are rows in your range. So
change the line to:
For i = 0 To orange.Rows.Count - 1

Inside your Trim function, if you re-arrange it as I suggest above, it will
only work if orange is a single column wide ... but if it is only one column
wide, then you're concatenating the same value to itself (which seems odd to
me). If the cell had a value of "pluto" then you would end up with
pluto(pluto)
in your list. Is that what you want? Is the bit inside the brackets supposed
to be the value in the second column? Also, the Trim would make more sense
around each range, rather than around the concatenation of the 2 ranges.
Try this instead for that line of code (all on one line, not wrapped like
this):
arrList(i).text = Trim(orange.Cells(i+1,1).Value) & " (" &
Trim(orange.Cells(i+1,2).Value) & ")"
That assumes you want the second column in the brackets. If you want the
first column (or if there is only one column), change the 2 to a 1. Also note
I didn't use your variable name of "list", as per my comment above.

Then you try to add an item to the list box that uses the variable orow...
but you haven't set orow to anything yet... and it looks like you're trying
to do the same as list(i).text... and later in the macro you add list(i).text
to the listbox... so I think the line:
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
can be deleted.

You have a line:
sort list
I don't know if an array can be sorted easily, but I'm certain that line
won't do it. For the time being, get rid of that line and see if you can get
it working without sorting the list. Deal with the sorting later.

You then have a little for..next loop to add the array items to the listbox
one at a time. That should work, but it won't replace any existing items in
the list (it will keep adding on to the existing list).
Instead, you can allocate the whole array to the list box at once:
Me.ListBox1.ControlFormat.List = arrList
(use that in place of the whole for-next loop, not just the line inside it)


"Stan" wrote:

Hi All

not my code, but I would love to fix it. The rows.value(i+1,1) --
statements are wrong.

How is it fixed please.

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Rows.Value(i + 1, 1) = "" Then Exit For
list(i).text = Trim(orange.Rows.Value(i + 1) & " (" &
orange.Rows.Value(i + 1) & ")")
list(i).id = orange.Rows.Value(i + 1)
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

Stan

Hi fixed

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Cells(i + 1, 1).Value = "" Then Exit For
list(i).text = Trim(orange.Cells(i + 1, 1).Value) & " " &
Trim(orange.Cells(i + 1, 2).Value)
list(i).id = orange.Cells(i + 1, 1).Value
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

"Diskdr" wrote:

"Cringing Dragon"

fantastic help and almost there, and I am sorry I forgot to give you the
range information.

The range has two columns and is acting as a look up, the first column ID
contains a code the second the description. Range is on a hidden sheet, and
its a timesheet system, you click on a button and the user form list box is
mention to open and you can then scroll down and select the project you are
working one.

As I have said not my code, but I have learned a lot more from your kind
help. If I comment out the line below, I get a list box user form with two
identical items say 203 (203), on selecting this I get 203 (in ID) and the
Description in the correct box, so alls I need to now is why the line below
deos not work, please.


Below is the latest code, which fails on

Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"

As you say its not set. The rest works fine, but not the expect outcome as
above.

list is not a key word so I get away with it.

Stan

New code

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Cells(i + 1, 1).Value = "" Then Exit For
list(i).text = Trim(orange.Cells(i + 1, 1).Value) & " (" &
Trim(orange.Cells(i + 1, 1).Value) & ")"
list(i).id = orange.Cells(i + 1, 1).Value
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

"Cringing Dragon" wrote:

You haven't told us exactly what you're trying to achieve, which makes it
harder for us to help. I gather that you have a named range (Proj_range) and
you want the values in that range to be added to a list box (Listbox1). What
is not clear is if that range is one column or more than one column, or if
you want the existing listbox added to or replaced with the new list. Also
there's apparently redundant code that I'm not sure if it's supposed to be
doing something or if it's leftovers from attempts to get it working.

---------------------------
Firstly, answering your question of why rows.value(i+1,1) doesn't work. It's
because the (i+1,1) parameter should be part of the range object, not part of
the .Value property.

In your for loop, i+1 represents a row number.
(i+1,1) represents the first cell of a row.
orange represents a range.
orange.Rows(i+1) will represent a particular row in the range (and if the
range is only 1 column wide this will be a single cell).
orange.Cells(i+1,1) will represent a particular cell in the range
orange.Cells(i+1,1).Value will represent the value (contents) of a cell in
the range.

So everywhere where you have:
orange.Rows.Value(i + 1, 1)
I think it should instead be:
orange.Cells(i+1,1).Value

And everywhere where you have:
orange.Rows.Value(i + 1)
I think it should instead be:
orange.Rows(i+1).Value
except in this case you're trying to get the "value" of a whole row of
orange, which will probably only work if orange is only one column wide. If
it's more than one column wide, and you want the value in the first column,
then instead use:
orange.Cells(i+1,1).Value

---------------------------
Secondly, you've used list as a variable name. List is a VBA keyword, so
it's a bad idea to try to use it as a variable name. So wherever you use
"list" in your code change it to something else - "arrList" for example.

Tip - to test to see if a variable name is "safe" to use, in the MS Visual
Basic Editor type the word on it's own in lower case on a line and hit enter.
If it stays lower case, then the editor has not recognised it as a special
word and its probably safe to use. If the editor does recognise the word, it
will change its case, usually so that it starts with an uppercase letter. If
you try that with list, the editor will turn it into List, which gives you a
clue that it's not a good variable name.

---------------------------
Thirdly, some suggestions for changing parts of your code.

In your first for...next loop you step from zero to the number of rows in
the range. Starting at zero instead of 1 makes some sense because you use i
in the array reference - and an array starts with element 0. However, because
you start at zero not 1 you need to stop at "orange.Rows.Count - 1",
otherwise you will have one more step than there are rows in your range. So
change the line to:
For i = 0 To orange.Rows.Count - 1

Inside your Trim function, if you re-arrange it as I suggest above, it will
only work if orange is a single column wide ... but if it is only one column
wide, then you're concatenating the same value to itself (which seems odd to
me). If the cell had a value of "pluto" then you would end up with
pluto(pluto)
in your list. Is that what you want? Is the bit inside the brackets supposed
to be the value in the second column? Also, the Trim would make more sense
around each range, rather than around the concatenation of the 2 ranges.
Try this instead for that line of code (all on one line, not wrapped like
this):
arrList(i).text = Trim(orange.Cells(i+1,1).Value) & " (" &
Trim(orange.Cells(i+1,2).Value) & ")"
That assumes you want the second column in the brackets. If you want the
first column (or if there is only one column), change the 2 to a 1. Also note
I didn't use your variable name of "list", as per my comment above.

Then you try to add an item to the list box that uses the variable orow...
but you haven't set orow to anything yet... and it looks like you're trying
to do the same as list(i).text... and later in the macro you add list(i).text
to the listbox... so I think the line:
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
can be deleted.

You have a line:
sort list
I don't know if an array can be sorted easily, but I'm certain that line
won't do it. For the time being, get rid of that line and see if you can get
it working without sorting the list. Deal with the sorting later.

You then have a little for..next loop to add the array items to the listbox
one at a time. That should work, but it won't replace any existing items in
the list (it will keep adding on to the existing list).
Instead, you can allocate the whole array to the list box at once:
Me.ListBox1.ControlFormat.List = arrList
(use that in place of the whole for-next loop, not just the line inside it)


"Stan" wrote:

Hi All

not my code, but I would love to fix it. The rows.value(i+1,1) --
statements are wrong.

How is it fixed please.

'fills in list box. stops at first empty row in project list
Private Sub UserForm_Initialize()
Dim orange As Range, orow As Range
Set orange = ThisWorkbook.Names("Proj_range").RefersToRange
'load each into a list and sort ascending

Dim i As Long
ReDim list(orange.Rows.Count)
For i = 0 To orange.Rows.Count
If orange.Rows.Value(i + 1, 1) = "" Then Exit For
list(i).text = Trim(orange.Rows.Value(i + 1) & " (" &
orange.Rows.Value(i + 1) & ")")
list(i).id = orange.Rows.Value(i + 1)
Me.ListBox1.AddItem orow.Value(1) & " (" & orow.Value(1) & ")"
Next
ReDim Preserve list(i - 1)
sort list
For i = 0 To UBound(list)
Me.ListBox1.AddItem list(i).text
Next
proj_id = 0
End Sub

Stan