I built a userform with 3 comboboxes and 2 command buttons on it.

This is the code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()

Dim iRow As Long
Dim NoDupes As Collection
Dim iCtr As Long

Set NoDupes = New Collection

With Me.ComboBox2
.Clear
.Enabled = False
End With

With Me.ComboBox3
.Clear
.Enabled = False
End With

If Me.ComboBox1.ListIndex < -1 Then
With Worksheets("Sheet1")
On Error Resume Next
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If LCase(.Cells(iRow, "A").Value) _
= LCase(Me.ComboBox1.Value) Then
NoDupes.Add .Cells(iRow, "b").Value, _
CStr(.Cells(iRow, "b").Value)
End If
Next iRow
On Error GoTo 0

If NoDupes.Count 0 Then
For iCtr = 1 To NoDupes.Count
Me.ComboBox2.AddItem NoDupes(iCtr)
Next iCtr
Me.ComboBox2.Enabled = True
Else
MsgBox "No choices for combobox2"
End If
End With
End If

End Sub

Private Sub ComboBox2_Change()

Dim iRow As Long
Dim NoDupes As Collection
Dim iCtr As Long

Set NoDupes = New Collection

With Me.ComboBox3
.Clear
.Enabled = False
End With

If Me.ComboBox2.ListIndex < -1 _
And Me.ComboBox1.ListIndex < -1 Then
With Worksheets("Sheet1")
On Error Resume Next
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If LCase(.Cells(iRow, "A").Value) _
= LCase(Me.ComboBox1.Value) Then
If LCase(.Cells(iRow, "B").Value) _
= LCase(Me.ComboBox2.Value) Then
NoDupes.Add .Cells(iRow, "c").Value, _
CStr(.Cells(iRow, "c").Value)
End If
End If
Next iRow
On Error GoTo 0

If NoDupes.Count 0 Then
For iCtr = 1 To NoDupes.Count
Me.ComboBox3.AddItem NoDupes(iCtr)
Next iCtr
Me.ComboBox3.Enabled = True
Else
MsgBox "No choices for combobox3"
End If
End With
End If

End Sub
Private Sub ComboBox3_Change()

If Me.ComboBox3.ListIndex < -1 _
And Me.ComboBox2.ListIndex < -1 _
And Me.ComboBox1.ListIndex < -1 Then
Me.CommandButton2.Enabled = True
Else
Me.CommandButton2.Enabled = False
End If

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()

Dim iRow As Long
Dim DestCell As Range

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

If Me.ComboBox3.ListIndex < -1 _
And Me.ComboBox2.ListIndex < -1 _
And Me.ComboBox1.ListIndex < -1 Then
With Worksheets("Sheet1")
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If LCase(.Cells(iRow, "A").Value) _
= LCase(Me.ComboBox1.Value) Then
If LCase(.Cells(iRow, "B").Value) _
= LCase(Me.ComboBox2.Value) Then
If LCase(.Cells(iRow, "C").Value) _
= LCase(Me.ComboBox3.Value) Then
'do the copy
.Rows(iRow).Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(1, 0)
End If
End If
End If
Next iRow
End With
End If

Call UserForm_Initialize

End Sub

Private Sub UserForm_Initialize()

Dim iRow As Long
Dim NoDupes As Collection
Dim iCtr As Long

With Me.ComboBox1
.Enabled = True
.Clear
.Style = fmStyleDropDownList
End With

With Me.ComboBox2
.Enabled = False
.Clear
.Style = fmStyleDropDownList
End With

With Me.ComboBox3
.Enabled = False
.Style = fmStyleDropDownList
End With


Set NoDupes = New Collection

With Worksheets("Sheet1")
On Error Resume Next
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
NoDupes.Add .Cells(iRow, "A").Value, CStr(.Cells(iRow, "A").Value)
Next iRow
On Error GoTo 0

If NoDupes.Count 0 Then
For iCtr = 1 To NoDupes.Count
Me.ComboBox1.AddItem NoDupes(iCtr)
Next iCtr
End If
End With

With Me.CommandButton1
.Enabled = True
.Caption = "Cancel"
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With

End Sub

I _think_ it does what you want.

And to show the userform you can put this in a general module:

Option Explicit
sub ShowMyForm()
userform1.show
end sub

You can plop a button from the Forms toolbar onto the worksheet and assign that
macro to the button or you can incorporate it into your code someother way.

You may want to review these two pages from Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
and
http://www.contextures.com/xlUserForm02.html

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Depending on how advanced you are...

Professional Excel Development
By Stephen Bullen, Rob Bovey, John Green

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.




William DeLeo wrote:

But, can I use autofilter without allowing the user to be inside the
particular sheet that holds the data? My client doesn't want the user
to have such access (only one record accessed at a time for data input
corrections). I just need to allow the user to identify one record and
then pull that info to another part of my workbook. But the user needs
three menu choices to adequately narrow down the list.

Regarding code ... I'm happy to go that route ... any suggestions for
functions that I could start with?

Thanks and sorry to drag this out ... I'll stop asking questions now
and taking advantage of your hospitality. Your help is greatly
appreciated.

WD

--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

--

Dave Peterson