I have a multi-select list box with the column count property set to 2
The program (before loading the form) sets the range name 'Contractors' to
cover two columns: The contractors' names and their respective VAT
registration numbers.

The form loads Rowsource with Factors!Contractors and shows the names of the
contractors and their VAT registration numbers. So far so good.
The user is asked to select one (or more) contractors.

I am trying to get the routine below to load the two (Public) string arrays:
Contractor(xx) and VatRegistration(xx) with the values wherever a
selection(s) is made:

Extract from the 'Run' button code:

Private Sub cmdRun_Click()
Dim i As Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i)
VatRegistration(i) = LBoxContractors.Text
End If
Next i
End If
End sub

The Contractor(i) loads up fine but the VatRegistration(i) has just a NUL
string (""). Can't seem to be able to assign the value in the 2nd column.

Any help or suggestions or ideas or better ways of doing this would be most
welcolm.

Thanks in advance,
Peter Bircher

Private Sub cmdRun_Click()
Dim i As Integer, j as Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
j = 0
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(j) = LBoxContractors.List(i,0)
VatRegistration(j) = LBoxContractors.List(i,1)
j = j + 1
End If
Next i
End If
End sub

--
Regards,
Tom Ogilvy


Peter wrote in message
...
I have a multi-select list box with the column count property set to 2
The program (before loading the form) sets the range name 'Contractors' to
cover two columns: The contractors' names and their respective VAT
registration numbers.

The form loads Rowsource with Factors!Contractors and shows the names of
the
contractors and their VAT registration numbers. So far so good.
The user is asked to select one (or more) contractors.

I am trying to get the routine below to load the two (Public) string
arrays:
Contractor(xx) and VatRegistration(xx) with the values wherever a
selection(s) is made:

Extract from the 'Run' button code:

Private Sub cmdRun_Click()
Dim i As Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i)
VatRegistration(i) = LBoxContractors.Text
End If
Next i
End If
End sub

The Contractor(i) loads up fine but the VatRegistration(i) has just a NUL
string (""). Can't seem to be able to assign the value in the 2nd column.

Any help or suggestions or ideas or better ways of doing this would be
most
welcolm.

Thanks in advance,
Peter Bircher

Tom, you are a star!

Once again, my sincerest thanks,
Peter


Tom Ogilvy wrote in message
...
Private Sub cmdRun_Click()
Dim i As Integer, j as Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
j = 0
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(j) = LBoxContractors.List(i,0)
VatRegistration(j) = LBoxContractors.List(i,1)
j = j + 1
End If
Next i
End If
End sub

--
Regards,
Tom Ogilvy


Peter wrote in message
...
I have a multi-select list box with the column count property set to 2
The program (before loading the form) sets the range name 'Contractors'
to
cover two columns: The contractors' names and their respective VAT
registration numbers.

The form loads Rowsource with Factors!Contractors and shows the names of
the
contractors and their VAT registration numbers. So far so good.
The user is asked to select one (or more) contractors.

I am trying to get the routine below to load the two (Public) string
arrays:
Contractor(xx) and VatRegistration(xx) with the values wherever a
selection(s) is made:

Extract from the 'Run' button code:

Private Sub cmdRun_Click()
Dim i As Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i)
VatRegistration(i) = LBoxContractors.Text
End If
Next i
End If
End sub

The Contractor(i) loads up fine but the VatRegistration(i) has just a
NUL
string (""). Can't seem to be able to assign the value in the 2nd
column.

Any help or suggestions or ideas or better ways of doing this would be
most
welcolm.

Thanks in advance,
Peter Bircher

When you use multi-select, then the listbox's .text property is empty. It won't
contain all the values you have selected. And watch out for the .listIndex
stuff with multiselect turned on. It just returns the row that has
focus--doesn't matter if that row was selected or not.

But you could get it this kind of way:

Option Explicit

Private Sub cmdRun_Click()

Dim i As Long
Dim Contractor() As String
Dim VatRegistration() As String

ReDim Contractor(0 To Me.LBoxContractors.ListCount - 1)
ReDim VatRegistration(0 To Me.LBoxContractors.ListCount - 1)

For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i, 0)
VatRegistration(i) = LBoxContractors.List(i, 1)
End If
Next i

End Sub

Private Sub UserForm_Initialize()

With Me.LBoxContractors
.List = Worksheets("sheet1").Range("a1:b5").Value
.MultiSelect = fmMultiSelectMulti
.BoundColumn = 1
'.TextColumn = 2
End With

End Sub

You'll end up with an array with the same number of elements as the the
listbox. Some elements may be blank if not checked.

Another way to just get an array of the selected items:
Option Explicit

Private Sub cmdRun_Click()
Dim Contractor() As String
Dim VatRegistration() As String
Dim i As Long
Dim selCtr As Long

selCtr = -1
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
selCtr = selCtr + 1
ReDim Preserve Contractor(0 To selCtr)
ReDim Preserve VatRegistration(0 To selCtr)
Contractor(selCtr) = LBoxContractors.List(i, 0)
VatRegistration(selCtr) = LBoxContractors.List(i, 1)
End If
Next i

If selCtr = -1 Then
MsgBox "nothing selected"
End If

End Sub

Then later on you could loop through that (probably) smaller array.




Peter wrote:

I have a multi-select list box with the column count property set to 2
The program (before loading the form) sets the range name 'Contractors' to
cover two columns: The contractors' names and their respective VAT
registration numbers.

The form loads Rowsource with Factors!Contractors and shows the names of the
contractors and their VAT registration numbers. So far so good.
The user is asked to select one (or more) contractors.

I am trying to get the routine below to load the two (Public) string arrays:
Contractor(xx) and VatRegistration(xx) with the values wherever a
selection(s) is made:

Extract from the 'Run' button code:

Private Sub cmdRun_Click()
Dim i As Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i)
VatRegistration(i) = LBoxContractors.Text
End If
Next i
End If
End sub

The Contractor(i) loads up fine but the VatRegistration(i) has just a NUL
string (""). Can't seem to be able to assign the value in the 2nd column.

Any help or suggestions or ideas or better ways of doing this would be most
welcolm.

Thanks in advance,
Peter Bircher

--

Dave Peterson

Hi Dave

Thanks for your detailed response.
I have implemented Tom's suggestion (has similar components) and it seems to
do the job very well. What I get is and array of only the selected items
which is very neat (as your second solutuion shows) - looks great - I think
I will will also give it a try - one learns *so much* from this newsgroup.

In you solution, is this correct?
'.TextColumn = 2
Should it be remmed out ?

I think I will bring in the redimentioned array variables as suggested -
saving some RAM!

Peter

Dave Peterson wrote in message
...
When you use multi-select, then the listbox's .text property is empty. It
won't
contain all the values you have selected. And watch out for the
..listIndex
stuff with multiselect turned on. It just returns the row that has
focus--doesn't matter if that row was selected or not.

But you could get it this kind of way:

Option Explicit

Private Sub cmdRun_Click()

Dim i As Long
Dim Contractor() As String
Dim VatRegistration() As String

ReDim Contractor(0 To Me.LBoxContractors.ListCount - 1)
ReDim VatRegistration(0 To Me.LBoxContractors.ListCount - 1)

For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i, 0)
VatRegistration(i) = LBoxContractors.List(i, 1)
End If
Next i

End Sub

Private Sub UserForm_Initialize()

With Me.LBoxContractors
.List = Worksheets("sheet1").Range("a1:b5").Value
.MultiSelect = fmMultiSelectMulti
.BoundColumn = 1
'.TextColumn = 2
End With

End Sub

You'll end up with an array with the same number of elements as the the
listbox. Some elements may be blank if not checked.

Another way to just get an array of the selected items:
Option Explicit

Private Sub cmdRun_Click()
Dim Contractor() As String
Dim VatRegistration() As String
Dim i As Long
Dim selCtr As Long

selCtr = -1
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
selCtr = selCtr + 1
ReDim Preserve Contractor(0 To selCtr)
ReDim Preserve VatRegistration(0 To selCtr)
Contractor(selCtr) = LBoxContractors.List(i, 0)
VatRegistration(selCtr) = LBoxContractors.List(i, 1)
End If
Next i

If selCtr = -1 Then
MsgBox "nothing selected"
End If

End Sub

Then later on you could loop through that (probably) smaller array.




Peter wrote:

I have a multi-select list box with the column count property set to 2
The program (before loading the form) sets the range name 'Contractors'
to
cover two columns: The contractors' names and their respective VAT
registration numbers.

The form loads Rowsource with Factors!Contractors and shows the names of
the
contractors and their VAT registration numbers. So far so good.
The user is asked to select one (or more) contractors.

I am trying to get the routine below to load the two (Public) string
arrays:
Contractor(xx) and VatRegistration(xx) with the values wherever a
selection(s) is made:

Extract from the 'Run' button code:

Private Sub cmdRun_Click()
Dim i As Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i)
VatRegistration(i) = LBoxContractors.Text
End If
Next i
End If
End sub

The Contractor(i) loads up fine but the VatRegistration(i) has just a
NUL
string (""). Can't seem to be able to assign the value in the 2nd
column.

Any help or suggestions or ideas or better ways of doing this would be
most
welcolm.

Thanks in advance,
Peter Bircher

--

Dave Peterson

If you toggle the multiselect off, then it'll be useful. But what did it always
return when you had multiselect turned on? Blanks. That's why I didn't figure
you needed it.





Peter wrote:

Hi Dave

Thanks for your detailed response.
I have implemented Tom's suggestion (has similar components) and it seems to
do the job very well. What I get is and array of only the selected items
which is very neat (as your second solutuion shows) - looks great - I think
I will will also give it a try - one learns *so much* from this newsgroup.

In you solution, is this correct?
'.TextColumn = 2
Should it be remmed out ?

I think I will bring in the redimentioned array variables as suggested -
saving some RAM!

Peter

Dave Peterson wrote in message
...
When you use multi-select, then the listbox's .text property is empty. It
won't
contain all the values you have selected. And watch out for the
.listIndex
stuff with multiselect turned on. It just returns the row that has
focus--doesn't matter if that row was selected or not.

But you could get it this kind of way:

Option Explicit

Private Sub cmdRun_Click()

Dim i As Long
Dim Contractor() As String
Dim VatRegistration() As String

ReDim Contractor(0 To Me.LBoxContractors.ListCount - 1)
ReDim VatRegistration(0 To Me.LBoxContractors.ListCount - 1)

For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i, 0)
VatRegistration(i) = LBoxContractors.List(i, 1)
End If
Next i

End Sub

Private Sub UserForm_Initialize()

With Me.LBoxContractors
.List = Worksheets("sheet1").Range("a1:b5").Value
.MultiSelect = fmMultiSelectMulti
.BoundColumn = 1
'.TextColumn = 2
End With

End Sub

You'll end up with an array with the same number of elements as the the
listbox. Some elements may be blank if not checked.

Another way to just get an array of the selected items:
Option Explicit

Private Sub cmdRun_Click()
Dim Contractor() As String
Dim VatRegistration() As String
Dim i As Long
Dim selCtr As Long

selCtr = -1
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
selCtr = selCtr + 1
ReDim Preserve Contractor(0 To selCtr)
ReDim Preserve VatRegistration(0 To selCtr)
Contractor(selCtr) = LBoxContractors.List(i, 0)
VatRegistration(selCtr) = LBoxContractors.List(i, 1)
End If
Next i

If selCtr = -1 Then
MsgBox "nothing selected"
End If

End Sub

Then later on you could loop through that (probably) smaller array.




Peter wrote:

I have a multi-select list box with the column count property set to 2
The program (before loading the form) sets the range name 'Contractors'
to
cover two columns: The contractors' names and their respective VAT
registration numbers.

The form loads Rowsource with Factors!Contractors and shows the names of
the
contractors and their VAT registration numbers. So far so good.
The user is asked to select one (or more) contractors.

I am trying to get the routine below to load the two (Public) string
arrays:
Contractor(xx) and VatRegistration(xx) with the values wherever a
selection(s) is made:

Extract from the 'Run' button code:

Private Sub cmdRun_Click()
Dim i As Integer
LBoxContractors.BoundColumn = 1
LBoxContractors.TextColumn = 2
If LBoxContractors.ListIndex < -1 Then
For i = 0 To LBoxContractors.ListCount - 1
If LBoxContractors.Selected(i) Then
Contractor(i) = LBoxContractors.List(i)
VatRegistration(i) = LBoxContractors.Text
End If
Next i
End If
End sub

The Contractor(i) loads up fine but the VatRegistration(i) has just a
NUL
string (""). Can't seem to be able to assign the value in the 2nd
column.

Any help or suggestions or ideas or better ways of doing this would be
most
welcolm.

Thanks in advance,
Peter Bircher

--

Dave Peterson


--

Dave Peterson