Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Building an Array
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 201
Default Building an Array
Excel 2002, WinXP
I have a problem building this array.
I have a UserForm with a ListBox.
The ListBox can have multiple items.
The list for the ListBox consists of one item this time.
The ListBox is setup for multiple selections.
The user selects the only item.
The statement "If UFNoMgtFee.lbNMFee.Selected(j) returns False, so no array
gets built.
What am I doing wrong?


Sub BuildArray()
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
Msg = ""
MsgBox "Hello" 'I get a Hello here so at least one item has been
selected
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
'Work with the array
End Sub


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 280
Default Building an Array
Works for me on Win2k, XL2002. Set a breakpoint where the msgbox is or on
the For, run til it stops, then look at things.

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have a problem building this array.
I have a UserForm with a ListBox.
The ListBox can have multiple items.
The list for the ListBox consists of one item this time.
The ListBox is setup for multiple selections.
The user selects the only item.
The statement "If UFNoMgtFee.lbNMFee.Selected(j) returns False, so no
array
gets built.
What am I doing wrong?


Sub BuildArray()
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
Msg = ""
MsgBox "Hello" 'I get a Hello here so at least one item has
been
selected
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
'Work with the array
End Sub




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Building an Array
I modified your code to show the form and it worked fine with 1 item
selected.

Is this code run after the form is Unloaded - if so, then the control has
been destroyed and the selection(s) are lost by then. I hide the form to
return to this code, then unload it here after getting the selection(s).

Sub BuildArray()
Dim MyArray()
UFNoMgtFee.Show
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
For i = LBound(MyArray) To UBound(MyArray)
Debug.Print i, MyArray(i)
Next
Unload UFNoMgtFee
'Work with the array
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have a problem building this array.
I have a UserForm with a ListBox.
The ListBox can have multiple items.
The list for the ListBox consists of one item this time.
The ListBox is setup for multiple selections.
The user selects the only item.
The statement "If UFNoMgtFee.lbNMFee.Selected(j) returns False, so no
array
gets built.
What am I doing wrong?


Sub BuildArray()
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
Msg = ""
MsgBox "Hello" 'I get a Hello here so at least one item has
been
selected
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
'Work with the array
End Sub




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 201
Default Building an Array
Tom
You are right on. I unload the form and then try to build the array.
I'll set it up the right way in the morning and see what I get. Thanks for
your help. Otto
"Tom Ogilvy" wrote in message
...
I modified your code to show the form and it worked fine with 1 item
selected.

Is this code run after the form is Unloaded - if so, then the control has
been destroyed and the selection(s) are lost by then. I hide the form to
return to this code, then unload it here after getting the selection(s).

Sub BuildArray()
Dim MyArray()
UFNoMgtFee.Show
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
For i = LBound(MyArray) To UBound(MyArray)
Debug.Print i, MyArray(i)
Next
Unload UFNoMgtFee
'Work with the array
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have a problem building this array.
I have a UserForm with a ListBox.
The ListBox can have multiple items.
The list for the ListBox consists of one item this time.
The ListBox is setup for multiple selections.
The user selects the only item.
The statement "If UFNoMgtFee.lbNMFee.Selected(j) returns False, so no
array
gets built.
What am I doing wrong?


Sub BuildArray()
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
Msg = ""
MsgBox "Hello" 'I get a Hello here so at least one item has
been
selected
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
'Work with the array
End Sub






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 201
Default Building an Array
Tom
My response didn't show up so I'll try again. You hit it on the head.
I was unloading the OF before I built the array. Thanks for your help.
Otto
"Tom Ogilvy" wrote in message
...
I modified your code to show the form and it worked fine with 1 item
selected.

Is this code run after the form is Unloaded - if so, then the control has
been destroyed and the selection(s) are lost by then. I hide the form to
return to this code, then unload it here after getting the selection(s).

Sub BuildArray()
Dim MyArray()
UFNoMgtFee.Show
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
For i = LBound(MyArray) To UBound(MyArray)
Debug.Print i, MyArray(i)
Next
Unload UFNoMgtFee
'Work with the array
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have a problem building this array.
I have a UserForm with a ListBox.
The ListBox can have multiple items.
The list for the ListBox consists of one item this time.
The ListBox is setup for multiple selections.
The user selects the only item.
The statement "If UFNoMgtFee.lbNMFee.Selected(j) returns False, so no
array
gets built.
What am I doing wrong?


Sub BuildArray()
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
Msg = ""
MsgBox "Hello" 'I get a Hello here so at least one item has
been
selected
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
'Work with the array
End Sub






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Building an Array
Your welcome.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Tom
My response didn't show up so I'll try again. You hit it on the head.
I was unloading the OF before I built the array. Thanks for your help.
Otto
"Tom Ogilvy" wrote in message
...
I modified your code to show the form and it worked fine with 1 item
selected.

Is this code run after the form is Unloaded - if so, then the control
has
been destroyed and the selection(s) are lost by then. I hide the form
to
return to this code, then unload it here after getting the selection(s).

Sub BuildArray()
Dim MyArray()
UFNoMgtFee.Show
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
For i = LBound(MyArray) To UBound(MyArray)
Debug.Print i, MyArray(i)
Next
Unload UFNoMgtFee
'Work with the array
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have a problem building this array.
I have a UserForm with a ListBox.
The ListBox can have multiple items.
The list for the ListBox consists of one item this time.
The ListBox is setup for multiple selections.
The user selects the only item.
The statement "If UFNoMgtFee.lbNMFee.Selected(j) returns False, so no
array
gets built.
What am I doing wrong?


Sub BuildArray()
ReDim MyArray(1 To 1000)
Co = 0
If UFNoMgtFee.lbNMFee.ListIndex = -1 Then
Msg = "Nothing"
Unload UFNoMgtFee
Else
Msg = ""
MsgBox "Hello" 'I get a Hello here so at least one item has
been
selected
For j = 0 To UFNoMgtFee.lbNMFee.ListCount - 1
If UFNoMgtFee.lbNMFee.Selected(j) Then
Msg = Msg & UFNoMgtFee.lbNMFee.List(j) & vbCrLf
Co = Co + 1
MyArray(Co) = UFNoMgtFee.lbNMFee.List(j)
End If
Next j
End If
If Co = 0 Then Exit Sub
ReDim Preserve MyArray(1 To Co)
'Work with the array
End Sub








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
meaning of : IF(Switch; Average(array A, array B); array A) DXAT Excel Worksheet Functions 1 October 24th 06 06:11 PM
Building a TOC phil1ray Excel Discussion (Misc queries) 2 February 10th 06 02:46 PM
Building Formulas Shawn K Excel Worksheet Functions 2 November 11th 05 12:06 AM
Building/Creating an Array Rich Cooper Excel Programming 8 May 26th 04 06:57 PM
Building xll No Name Excel Programming 2 February 28th 04 06:25 PM


All times are GMT +1. The time now is 12:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement