Hi Guys,
I have a userform with a multipage and 3 listboxes. with one listbox being populated with data from a sheet. When the user selects an item from the populated list box(listbox6) it will do a search of one sheet and return the value to one listbox(listbox10),and then do another search of the second sheet and return the value to the other listbox(listbox24).
My problem is i am getting duplicate results in my second listbox(listbox24) from the first sheet search. Basically i'm running two codes one after the other and return the results in two listboxes from two separate sheets.
Mark
Private Sub ListBox6_Click()
Dim r As Range, ff As String, a(), n As Long, i As Integer
With AdminNewOrder.MultiPage1.Page3
ListBox10.Clear
ListBox24.Clear
TextBox8.Value = ""
End With
If Len(Me.ListBox6.Text) = 0 Then Exit Sub
With Sheets("Orders")
Set r = .Columns("C").Find(Me.ListBox6.Text, , , xlWhole)
If r Is Nothing Then
Exit Sub
End If
ff = r.Address
Do
n = n + 1
ReDim Preserve a(1 To 6, 1 To n)
For i = 1 To 6
a(i, n) = r.Offset(, i - 3).Value
Next
Set r = .Columns("C").FindNext(r)
Loop Until ff = r.Address
End With
With Me.ListBox10
.ColumnCount = 7
.ColumnWidths = "49.95 pt;55 pt;110 pt;50 pt;50 pt;50 pt"
.Column = a
End With
With Sheets("ArchivedOrders")
Set r = .Columns("C").Find(Me.ListBox6.Text, , , xlWhole)
If r Is Nothing Then
Exit Sub
End If
ff = r.Address
Do
n = n + 1
ReDim Preserve a(1 To 6, 1 To n)
For i = 1 To 6
a(i, n) = r.Offset(, i - 3).Value
Next
Set r = .Columns("C").FindNext(r)
Loop Until ff = r.Address
End With
With Me.ListBox24
.ColumnCount = 7
.ColumnWidths = "49.95 pt;55 pt;110 pt;50 pt;50 pt;50 pt"
.Column = a
End With
End Sub
I have a userform with a multipage and 3 listboxes. with one listbox being populated with data from a sheet. When the user selects an item from the populated list box(listbox6) it will do a search of one sheet and return the value to one listbox(listbox10),and then do another search of the second sheet and return the value to the other listbox(listbox24).
My problem is i am getting duplicate results in my second listbox(listbox24) from the first sheet search. Basically i'm running two codes one after the other and return the results in two listboxes from two separate sheets.
Mark
Private Sub ListBox6_Click()
Dim r As Range, ff As String, a(), n As Long, i As Integer
With AdminNewOrder.MultiPage1.Page3
ListBox10.Clear
ListBox24.Clear
TextBox8.Value = ""
End With
If Len(Me.ListBox6.Text) = 0 Then Exit Sub
With Sheets("Orders")
Set r = .Columns("C").Find(Me.ListBox6.Text, , , xlWhole)
If r Is Nothing Then
Exit Sub
End If
ff = r.Address
Do
n = n + 1
ReDim Preserve a(1 To 6, 1 To n)
For i = 1 To 6
a(i, n) = r.Offset(, i - 3).Value
Next
Set r = .Columns("C").FindNext(r)
Loop Until ff = r.Address
End With
With Me.ListBox10
.ColumnCount = 7
.ColumnWidths = "49.95 pt;55 pt;110 pt;50 pt;50 pt;50 pt"
.Column = a
End With
With Sheets("ArchivedOrders")
Set r = .Columns("C").Find(Me.ListBox6.Text, , , xlWhole)
If r Is Nothing Then
Exit Sub
End If
ff = r.Address
Do
n = n + 1
ReDim Preserve a(1 To 6, 1 To n)
For i = 1 To 6
a(i, n) = r.Offset(, i - 3).Value
Next
Set r = .Columns("C").FindNext(r)
Loop Until ff = r.Address
End With
With Me.ListBox24
.ColumnCount = 7
.ColumnWidths = "49.95 pt;55 pt;110 pt;50 pt;50 pt;50 pt"
.Column = a
End With
End Sub