Listbox

  • Thread starter brownti via OfficeKB.com
  • Start date
B

brownti via OfficeKB.com

I am using the code below to create a search form. I have it to the point i
want, however the second column is not showing up on the listbox, all other
columns are. I know there is just a little snipit of code i am missing, but
cant figure out where. Any help is very much appreciated. Thanks.

Private Sub CommandButton1_Click()
Dim myCell As Range
Dim VisNameRng As Range
Dim StrToFind As String
Dim iCol As Long

Me.ListBox1.Clear

If Trim(Me.SearchBox.Value) = "" Then
Beep
Exit Sub
End If

StrToFind = Me.SearchBox.Value
myRng.Parent.AutoFilterMode = False

If Me.CheckBox1.Value = True Then
StrToFind = "*" & StrToFind & "*"
End If

If DateSearch = True Then
With myRng
'Search in column C
Set myNameRng = .Columns(3)
End With
With myNameRng
.AutoFilter field:=1, Criteria1:=StrToFind
Set VisNameRng = Nothing
On Error Resume Next
Set VisNameRng _
= .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

If VisNameRng Is Nothing Then
MsgBox "Name not found!"
Exit Sub
End If

For Each myCell In VisNameRng.Cells
With Me.ListBox1
.AddItem myCell.Offset(, -2).Value
For iCol = 1 To myRng.Columns.Count
.List(.ListCount - 1, iCol - -1) _
= myCell.Offset(0, iCol - 1).Text
Next iCol
End With
Next myCell
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top