I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

Hi

You have to build an array to be pasted to the listbox. Remeber to put
"Option Base 1" at the very top of the userform module.

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve MyArray(2, UBound(MyArray, 2) + 1)
End If
ListArray(1, UBound(MyArray, 2)) = SAPc.Value
ListArray(2, UBound(MyArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

Hopes this helps.

---
Per

"Ayo" skrev i meddelelsen
...
I have a userform with a 2 column listbox and I have a 2 column range in
a
worksheet. I am trying to load specific rows in my worksheet range into
the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

I am getting a "Invalid ReDim" error on ReDim Preserve MyArray(2,. What do
this mean?

"Per Jessen" wrote:

Hi

You have to build an array to be pasted to the listbox. Remeber to put
"Option Base 1" at the very top of the userform module.

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve MyArray(2, UBound(MyArray, 2) + 1)
End If
ListArray(1, UBound(MyArray, 2)) = SAPc.Value
ListArray(2, UBound(MyArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

Hopes this helps.

---
Per

"Ayo" skrev i meddelelsen
...
I have a userform with a 2 column listbox and I have a 2 column range in
a
worksheet. I am trying to load specific rows in my worksheet range into
the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

My fault, I renamed the variable, to ListArray, but forgot to rename all
instances of the variable.

This should work:

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve ListArray(2, UBound(ListArray, 2) +
1)
End If
ListArray(1, UBound(ListArray, 2)) = SAPc.Value
ListArray(2, UBound(ListArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

-Per

"Ayo" skrev i meddelelsen
...
I am getting a "Invalid ReDim" error on ReDim Preserve MyArray(2,. What do
this mean?

"Per Jessen" wrote:

Hi

You have to build an array to be pasted to the listbox. Remeber to put
"Option Base 1" at the very top of the userform module.

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve MyArray(2, UBound(MyArray, 2) + 1)
End If
ListArray(1, UBound(MyArray, 2)) = SAPc.Value
ListArray(2, UBound(MyArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

Hopes this helps.

---
Per

"Ayo" skrev i meddelelsen
...
I have a userform with a 2 column listbox and I have a 2 column range
in
a
worksheet. I am trying to load specific rows in my worksheet range into
the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0,
1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

You could also use:

If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
with ufrmsiteinfo.listbox5
.additem SAPc.Value
.list(.listcount-1,1) = SAPc.Offset(0, 1).Value
end with
Exit For 'just one item????
End If



Ayo wrote:

I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

--

Dave Peterson

Thanks Per but I am only getting result from the first column, the
ListArray(1, UBound(ListArray, 2)) = SAPc.Value
none of the
ListArray(2, UBound(ListArray, 2)) = SAPc.Offset(0, 1).Value are showing up
in the listbox. Any ideas?





"Per Jessen" wrote:

My fault, I renamed the variable, to ListArray, but forgot to rename all
instances of the variable.

This should work:

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve ListArray(2, UBound(ListArray, 2) +
1)
End If
ListArray(1, UBound(ListArray, 2)) = SAPc.Value
ListArray(2, UBound(ListArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

-Per

"Ayo" skrev i meddelelsen
...
I am getting a "Invalid ReDim" error on ReDim Preserve MyArray(2,. What do
this mean?

"Per Jessen" wrote:

Hi

You have to build an array to be pasted to the listbox. Remeber to put
"Option Base 1" at the very top of the userform module.

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve MyArray(2, UBound(MyArray, 2) + 1)
End If
ListArray(1, UBound(MyArray, 2)) = SAPc.Value
ListArray(2, UBound(MyArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

Hopes this helps.

---
Per

"Ayo" skrev i meddelelsen
...
I have a userform with a 2 column listbox and I have a 2 column range
in
a
worksheet. I am trying to load specific rows in my worksheet range into
the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0,
1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

Add a watch to the ListArray variable and insert a break point after the
look, run the code and check the values in the variable. If there is no
values in second "column" then the macro don't pick up any values from the
column next to SAPc.

Also try to move the scroll bar at the bottum of the listbox, maybe the
second column just isn't visible.

-Per

"Ayo" skrev i meddelelsen
...
Thanks Per but I am only getting result from the first column, the
ListArray(1, UBound(ListArray, 2)) = SAPc.Value
none of the
ListArray(2, UBound(ListArray, 2)) = SAPc.Offset(0, 1).Value are showing
up
in the listbox. Any ideas?





"Per Jessen" wrote:

My fault, I renamed the variable, to ListArray, but forgot to rename all
instances of the variable.

This should work:

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value = ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve ListArray(2, UBound(ListArray, 2)
+
1)
End If
ListArray(1, UBound(ListArray, 2)) = SAPc.Value
ListArray(2, UBound(ListArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

-Per

"Ayo" skrev i meddelelsen
...
I am getting a "Invalid ReDim" error on ReDim Preserve MyArray(2,. What
do
this mean?

"Per Jessen" wrote:

Hi

You have to build an array to be pasted to the listbox. Remeber to put
"Option Base 1" at the very top of the userform module.

Option Base 1
Sub test()
Dim ListArray()
Dim counter As Long
ReDim ListArray(2, 1)

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5. _
Offset(0, 1).Value =
ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
If ListArray(1, 1) < "" Then
ReDim Preserve MyArray(2, UBound(MyArray, 2) +
1)
End If
ListArray(1, UBound(MyArray, 2)) = SAPc.Value
ListArray(2, UBound(MyArray, 2)) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5
ufrmsiteInfo.ListBox5.Column = ListArray

End Sub

Hopes this helps.

---
Per

"Ayo" skrev i meddelelsen
...
I have a userform with a 2 column listbox and I have a 2 column
range
in
a
worksheet. I am trying to load specific rows in my worksheet range
into
the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0,
1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0,
1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

Not one item, just one item at a time. I am looping through the range and
checking some conditions and when these conditions are met then I want to
loop through the range that fills in the list that is why I inserted the exit
for so that I can start back from the begining. I will try this. I hope this
works.

"Dave Peterson" wrote:

You could also use:

If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
with ufrmsiteinfo.listbox5
.additem SAPc.Value
.list(.listcount-1,1) = SAPc.Offset(0, 1).Value
end with
Exit For 'just one item????
End If



Ayo wrote:

I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

--

Dave Peterson

With this code, nothing is showing up in the listbox. I suspect that I am
doing something wrong, or at least missing a step or two, but I can't figure
out what they are. Anymore guidiance you can provide will be really
appreciated.
Thanks

"Dave Peterson" wrote:

You could also use:

If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
with ufrmsiteinfo.listbox5
.additem SAPc.Value
.list(.listcount-1,1) = SAPc.Offset(0, 1).Value
end with
Exit For 'just one item????
End If



Ayo wrote:

I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

--

Dave Peterson

Never mind. It works perfect. I found the mistake. I left a line of code in
there that shouldn't have been in at all.
Thanks a bunch Dave. You are the best.

"Dave Peterson" wrote:

You could also use:

If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
with ufrmsiteinfo.listbox5
.additem SAPc.Value
.list(.listcount-1,1) = SAPc.Offset(0, 1).Value
end with
Exit For 'just one item????
End If



Ayo wrote:

I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

--

Dave Peterson

Another question. I am trying to see if I can add headers to the list box.
Ebvery one said it is not possible unless the listbox is bound using
rowsource/listfillrange property. I found this code snippet:

Dim oLB As clsListBoxHeader
Dim aHeaders As Variant
aHeaders = Array("Head1", "Head2")
Set oLB = New clsListBoxHeader
With oLB
.Create(ufrmsiteInfo.ListBox5, aHeaders)
.BackColor = RGB(255, 0, 0)
End With
Set oLB = Nothing '(Header will remain intact)

from
http://groups.google.com/groups?selm...g .google.com
I tried it but something is missing on this line,
..Create(ufrmsiteInfo.ListBox5, aHeaders)
and I also have an issue with "Dim oLB As clsListBoxHeader"

Any ideas?
Thanks


"Dave Peterson" wrote:

You could also use:

If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
with ufrmsiteinfo.listbox5
.additem SAPc.Value
.list(.listcount-1,1) = SAPc.Offset(0, 1).Value
end with
Exit For 'just one item????
End If



Ayo wrote:

I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

--

Dave Peterson

You'll need to create a class module and put that code in there. But worse,
you'll have to find the rest of the code to make it work.

Other options would be to use labels as headers (right above the listbox). Or
extract your selected range to a different (temporary) worksheet and use that
range as the rowsource.

Ayo wrote:

Another question. I am trying to see if I can add headers to the list box.
Ebvery one said it is not possible unless the listbox is bound using
rowsource/listfillrange property. I found this code snippet:

Dim oLB As clsListBoxHeader
Dim aHeaders As Variant
aHeaders = Array("Head1", "Head2")
Set oLB = New clsListBoxHeader
With oLB
.Create(ufrmsiteInfo.ListBox5, aHeaders)
.BackColor = RGB(255, 0, 0)
End With
Set oLB = Nothing '(Header will remain intact)

from
http://groups.google.com/groups?selm...g .google.com
I tried it but something is missing on this line,
.Create(ufrmsiteInfo.ListBox5, aHeaders)
and I also have an issue with "Dim oLB As clsListBoxHeader"

Any ideas?
Thanks

"Dave Peterson" wrote:

You could also use:

If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
with ufrmsiteinfo.listbox5
.additem SAPc.Value
.list(.listcount-1,1) = SAPc.Offset(0, 1).Value
end with
Exit For 'just one item????
End If



Ayo wrote:

I have a userform with a 2 column listbox and I have a 2 column range in a
worksheet. I am trying to load specific rows in my worksheet range into the
listbox. So for instance if
my worksheet range is Range("B2:C10"), and
Range("B3:C3"), Range("B5:C5") and Range("B8:C8") meets my specific
conditions,
I want to load this ranges into my 2 column listbox

How can I do this? So far this is what I have:

For Each c5 In dbeWS.Range("B2:B" & rngSAPlines).Cells
If c5.Value = ufrmsiteInfo.cmbSiteID.Text And c5.Offset(0, 1).Value
= ufrmsiteInfo.txtbox3.Text _
And c5.Offset(0, 4).Value = False Then
For Each SAPc In smlWS.Range("B2:B" & smllastRow).Cells
If CStr(SAPc.Value) = c5.Offset(0, 3).Value Then
ufrmsiteInfo.ListBox5.Column(0) = SAPc.Value
ufrmsiteInfo.ListBox5.Column(1) = SAPc.Offset(0, 1).Value
Exit For
End If
Next SAPc
End If
Next c5

And this is not getting me anywhere. Please help.

--

Dave Peterson


--

Dave Peterson