I put my "address book" in sheet1.
I added a sheet2 and put some instructions on that worksheet--along with a
button from the Forms toolbar that had a macro assigned to it that would show
the userform.
This was the macro (located in a general module):
Option Explicit
Sub testme()
UserForm1.Show
End Sub
I had headers in Row 1. Last Name in column A and other stuff in B:F (6 columns
total).
Then I created a userform.
I had one textbox, one checkbox, one listbox and two buttons.
The textbox was used to get the name to search for.
The checkbox was used to indicate contains or exact (checked means contains)
The listbox returned all the names that matched--and its associated columns
(B:F).
The commandbuttons did the work or closed the userform.
This was the code behind the userform:
Option Explicit
Dim myRng As Range
Dim myNameRng As Range
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.TextBox1.Value) = "" Then
Beep
Exit Sub
End If
StrToFind = Me.TextBox1.Value
myRng.Parent.AutoFilterMode = False
If Me.CheckBox1.Value = True Then
StrToFind = "*" & StrToFind & "*"
End If
With myRng
'lastname in column A
Set myNameRng = .Columns(1)
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.Value
For iCol = 2 To myRng.Columns.Count
.List(.ListCount - 1, iCol - 1) _
= myCell.Offset(0, iCol - 1).Text
Next iCol
End With
Next myCell
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Worksheets("sheet1")
'resized to 6 columns
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 6)
End With
Me.ListBox1.ColumnCount = myRng.Columns.Count
Me.CommandButton1.Caption = "Go"
Me.CommandButton2.Caption = "Cancel"
Me.CheckBox1.Caption = "Contains?"
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
And if you're new to Userforms, you may want to read Debra Dalgleish's notes at:
http://www.contextures.com/xlUserForm01.html