I was only intending to show that you can isolate a row based on the user
ListBox selection. You'd want to do something with that record, then
disengage the AutoFilter. There are also other alternatives to using the
AutoFilter, but I started there because many people are familiar with its
functionality.

Try this edited code:

Private Sub cmdPullSelectedData_Click()
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3

With lbxCustName
If .ListIndex < -1 Then
strCrit_1 = .List(.ListIndex, 0)
strCrit_2 = .List(.ListIndex, 1)
strCrit_3 = .List(.ListIndex, 2)
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3

MsgBox strCrit_1 & " " & strCrit_2 & " " & strCrit_3

'Do something with the data here...then turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Joanne" wrote:

Ron
How do I return my table to it's original state.
The only records showing are the first one, with drop down arrows in the
fields, and the isolated record.

I need to get to the table because I must add more info the the records
Thanks again
Joanne
Ron Coderre wrote:

Joanne

There was a flow control flaw in my posted code, but it shouldn't have
caused a problem...

In any case, incorporating your posted code and mine:

Private Sub cmdPullSelectedData_Click()
Dim wbNew As Workbook
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3

With lbxCustName
If .ListIndex < -1 Then
strCrit_1 = .List(.ListIndex, 0)
strCrit_2 = .List(.ListIndex, 1)
strCrit_3 = .List(.ListIndex, 2)
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
End With
End If
End With
Label1.Caption = strCrit_1 & " " & strCrit_2 & " " & strCrit_3
End Sub

Also...here are some alternatives to playing with Label1:
MsgBox strCrit_1 & " " & strCrit_2 & " " & strCrit_3

or...even better...
Set a break point at: With Range("rngAllData")
When the code pauses there...hover your mouse cursor over each criteria.
It's value will display.

or...
type this in the Immediate Window (then press enter):
? strCrit_1

When you're ready to let the code finish...
Press the [F5] key

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Joanne" wrote:

Hi Ron

Trying to use your code but cannot get the result to print to
Label1.caption so that I can see if it is grabbing what is expected.

Here is how I wrote the code to show the record data in label1:

With lbxCustName
If .ListIndex < -1 Then
strCrit_1 = .List(.ListIndex, 0) 'Custname
strCrit_2 = .List(.ListIndex, 1) 'CustStreet
strCrit_3 = .List(.ListIndex, 2) 'CustCity
End If
End With

With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
End With
End Sub

Label1.Caption = strCrit_1 & " " & strCrit_2 & " " & strCrit_3
Nothing shows up in the test label - is there some other method I should
use to be able to study the results to be sure I am getting the expected
data?

Thanks for your time and knowledge

Ron Coderre wrote:

Private Sub cmdPullSelectedData_Click()
Dim wbNew As Workbook
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3

With lbxCustName
If .ListIndex < -1 Then
strCrit_1 = .List(.ListIndex, 0) 'Custname
strCrit_2 = .List(.ListIndex, 1) 'CustStreet
strCrit_3 = .List(.ListIndex, 2) 'CustCity
End If
End With

With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
End With
End Sub