Using MSOfficePro 2003, winxp proSP2

I want to generate a worksheet by grabbing some sheets from a different
wb - then dumping certain columns from the sheets I grabbed.

I have a table that contains CustName, CustStreet, CustCity, Vendor1,
Vendor2, Vendor3 (etc - it will vary by customer and the purpose of this
is to tell me which sheets to grab from the other wb) and I will always
be grabbing ColA, ColB and ColC plus one more column from the sheets I
keep from the original.

This table loads in a listbox so the user can make the choice which
customer they need to work with. The list box shows only the first 3
columns, but when the user clicks on a choice, I will need to capture
the information for the entire record.

My questions a
How (and where in the wb does the code go) do I capture the choice the
user makes. I think the code will go in the lstBox.click event and the
choice will be referenced by List1.ListIndex. That is about all I know
right now, and I am really wondering if the List1.ListIndex entry will
contain all of the fields present in the record - if it does, how do I
access the different fields in the record so that I can use them to
manipulate the dumping of certain sheets and columns in the original
workbook so the user gets only the relevant info?

If you know of a good example on the net or amongst the excel mvps that
I could study to help myself, I sure would appreciate being pointed in
that direction.

As always, thank you for your time and expertise
Joanne

I found this bit of code on the net while reading thru the groups for
help and I adapted it a bit to fit my (hoped for) app:

It is code to capture the list box choice and it shows me the results in
a label just to test what is happening.

Private Sub LstBoxCustInfo_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String

Set SourceData = Range("MyDataRange")

Val1 = LstBoxCustInfo.Value
Val2 = SourceData.Offset(LstBoxCustInfo.ListIndex, 1).Resize(1,
1).Value
Val3 = SourceData.Offset(LstBoxCustInfo.ListIndex, 2).Resize(1,
1).Value

Label1.Caption = Val1 & " " & Val2 & " " & Val3
End Sub

Well, it captures the first column correctly, but I can't figure out
where it is getting the info in the second and third columns. The info
it is grabbing for val2 and val3 is correct in that it is from the
proper columns, but it is incorrect in that it does not belong to the
record chosen in the first column.

Any ideas?

Also, if I need the data from all the columns on my table, must I
include all these columns in the list box - I expect so but I sure would
like to be able to do it in a manner that keeps them hidden from the
user's eyes. Could I achieve this by having a '0' width value for the
columns I don't want to show in the listbox, yet I would be able to
manipulate the data to get what I want for the user?

One more question please:
what does .Resize(1,1) do

Thank you

Joanne wrote:

Using MSOfficePro 2003, winxp proSP2

I want to generate a worksheet by grabbing some sheets from a different
wb - then dumping certain columns from the sheets I grabbed.

I have a table that contains CustName, CustStreet, CustCity, Vendor1,
Vendor2, Vendor3 (etc - it will vary by customer and the purpose of this
is to tell me which sheets to grab from the other wb) and I will always
be grabbing ColA, ColB and ColC plus one more column from the sheets I
keep from the original.

This table loads in a listbox so the user can make the choice which
customer they need to work with. The list box shows only the first 3
columns, but when the user clicks on a choice, I will need to capture
the information for the entire record.

My questions a
How (and where in the wb does the code go) do I capture the choice the
user makes. I think the code will go in the lstBox.click event and the
choice will be referenced by List1.ListIndex. That is about all I know
right now, and I am really wondering if the List1.ListIndex entry will
contain all of the fields present in the record - if it does, how do I
access the different fields in the record so that I can use them to
manipulate the dumping of certain sheets and columns in the original
workbook so the user gets only the relevant info?

If you know of a good example on the net or amongst the excel mvps that
I could study to help myself, I sure would appreciate being pointed in
that direction.

As always, thank you for your time and expertise
Joanne

In an attempt to solve this problem I added the line 8 to the code

Private Sub LstBoxCustInfo_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String

Set SourceData = Range("MyDataRange")

Val1 = LstBoxCustInfo.Value
Val2 = SourceData.Offset(LstBoxCustInfo.ListIndex, 1).Resize(1,
1).Value
Val3 = SourceData.Offset(LstBoxCustInfo.ListIndex, 2).Resize(1,
1).Value


(LIne 8)
Val4 = SourceData.Offset(LstBoxCustInfo.ListIndex, 3).Resize(1,
1).Value


Label1.Caption = Val1 & " " & Val2 & " " & Val3 & " " & Val4
End Sub

I now get all 3 fields of data together for the record - but I am
getting the wrong record. I have all the records that begin with letter
A in the list box, if I click on, say record 4, instead of getting
record 4 in the list box I am getting record 4 from the table. I tried
using Val1 = LstBoxCustInfo.ListIndex but that didn't do it - all that
did was print the index number of the record on the table.

Could someone please show me how to get the correct record returned when
the user chooses one from the listbox?

Thanks for your time and expertise

Joanne wrote:

I found this bit of code on the net while reading thru the groups for
help and I adapted it a bit to fit my (hoped for) app:

It is code to capture the list box choice and it shows me the results in
a label just to test what is happening.

Private Sub LstBoxCustInfo_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String

Set SourceData = Range("MyDataRange")

Val1 = LstBoxCustInfo.Value
Val2 = SourceData.Offset(LstBoxCustInfo.ListIndex, 1).Resize(1,
1).Value
Val3 = SourceData.Offset(LstBoxCustInfo.ListIndex, 2).Resize(1,
1).Value

Label1.Caption = Val1 & " " & Val2 & " " & Val3
End Sub

Well, it captures the first column correctly, but I can't figure out
where it is getting the info in the second and third columns. The info
it is grabbing for val2 and val3 is correct in that it is from the
proper columns, but it is incorrect in that it does not belong to the
record chosen in the first column.

Any ideas?

Also, if I need the data from all the columns on my table, must I
include all these columns in the list box - I expect so but I sure would
like to be able to do it in a manner that keeps them hidden from the
user's eyes. Could I achieve this by having a '0' width value for the
columns I don't want to show in the listbox, yet I would be able to
manipulate the data to get what I want for the user?

One more question please:
what does .Resize(1,1) do

Thank you

Joanne wrote:

Using MSOfficePro 2003, winxp proSP2

I want to generate a worksheet by grabbing some sheets from a different
wb - then dumping certain columns from the sheets I grabbed.

I have a table that contains CustName, CustStreet, CustCity, Vendor1,
Vendor2, Vendor3 (etc - it will vary by customer and the purpose of this
is to tell me which sheets to grab from the other wb) and I will always
be grabbing ColA, ColB and ColC plus one more column from the sheets I
keep from the original.

This table loads in a listbox so the user can make the choice which
customer they need to work with. The list box shows only the first 3
columns, but when the user clicks on a choice, I will need to capture
the information for the entire record.

My questions a
How (and where in the wb does the code go) do I capture the choice the
user makes. I think the code will go in the lstBox.click event and the
choice will be referenced by List1.ListIndex. That is about all I know
right now, and I am really wondering if the List1.ListIndex entry will
contain all of the fields present in the record - if it does, how do I
access the different fields in the record so that I can use them to
manipulate the dumping of certain sheets and columns in the original
workbook so the user gets only the relevant info?

If you know of a good example on the net or amongst the excel mvps that
I could study to help myself, I sure would appreciate being pointed in
that direction.

As always, thank you for your time and expertise
Joanne

Hi, Joanne!

The ListIndex refes to the item in the Listbox. Consequently, ListIndex 1
refers to the 2nd item in the list....which may be the 10th item in the
source data.

Try something like this:

Define a Range Name that includes ALL of the data in the source data range
(I used "rngAllData")

Then....put a button (named "cmdPullSelectedData") on the Userform that
engages this code:

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

That code sets an autofilter on the source data and isolates the selected
item's row data.

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

XL2002, WinXP


"Joanne" wrote:

In an attempt to solve this problem I added the line 8 to the code

Private Sub LstBoxCustInfo_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String

Set SourceData = Range("MyDataRange")

Val1 = LstBoxCustInfo.Value
Val2 = SourceData.Offset(LstBoxCustInfo.ListIndex, 1).Resize(1,
1).Value
Val3 = SourceData.Offset(LstBoxCustInfo.ListIndex, 2).Resize(1,
1).Value


(LIne 8)
Val4 = SourceData.Offset(LstBoxCustInfo.ListIndex, 3).Resize(1,
1).Value


Label1.Caption = Val1 & " " & Val2 & " " & Val3 & " " & Val4
End Sub

I now get all 3 fields of data together for the record - but I am
getting the wrong record. I have all the records that begin with letter
A in the list box, if I click on, say record 4, instead of getting
record 4 in the list box I am getting record 4 from the table. I tried
using Val1 = LstBoxCustInfo.ListIndex but that didn't do it - all that
did was print the index number of the record on the table.

Could someone please show me how to get the correct record returned when
the user chooses one from the listbox?

Thanks for your time and expertise

Joanne wrote:

I found this bit of code on the net while reading thru the groups for
help and I adapted it a bit to fit my (hoped for) app:

It is code to capture the list box choice and it shows me the results in
a label just to test what is happening.

Private Sub LstBoxCustInfo_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String

Set SourceData = Range("MyDataRange")

Val1 = LstBoxCustInfo.Value
Val2 = SourceData.Offset(LstBoxCustInfo.ListIndex, 1).Resize(1,
1).Value
Val3 = SourceData.Offset(LstBoxCustInfo.ListIndex, 2).Resize(1,
1).Value

Label1.Caption = Val1 & " " & Val2 & " " & Val3
End Sub

Well, it captures the first column correctly, but I can't figure out
where it is getting the info in the second and third columns. The info
it is grabbing for val2 and val3 is correct in that it is from the
proper columns, but it is incorrect in that it does not belong to the
record chosen in the first column.

Any ideas?

Also, if I need the data from all the columns on my table, must I
include all these columns in the list box - I expect so but I sure would
like to be able to do it in a manner that keeps them hidden from the
user's eyes. Could I achieve this by having a '0' width value for the
columns I don't want to show in the listbox, yet I would be able to
manipulate the data to get what I want for the user?

One more question please:
what does .Resize(1,1) do

Thank you

Joanne wrote:

Using MSOfficePro 2003, winxp proSP2

I want to generate a worksheet by grabbing some sheets from a different
wb - then dumping certain columns from the sheets I grabbed.

I have a table that contains CustName, CustStreet, CustCity, Vendor1,
Vendor2, Vendor3 (etc - it will vary by customer and the purpose of this
is to tell me which sheets to grab from the other wb) and I will always
be grabbing ColA, ColB and ColC plus one more column from the sheets I
keep from the original.

This table loads in a listbox so the user can make the choice which
customer they need to work with. The list box shows only the first 3
columns, but when the user clicks on a choice, I will need to capture
the information for the entire record.

My questions a
How (and where in the wb does the code go) do I capture the choice the
user makes. I think the code will go in the lstBox.click event and the
choice will be referenced by List1.ListIndex. That is about all I know
right now, and I am really wondering if the List1.ListIndex entry will
contain all of the fields present in the record - if it does, how do I
access the different fields in the record so that I can use them to
manipulate the dumping of certain sheets and columns in the original
workbook so the user gets only the relevant info?

If you know of a good example on the net or amongst the excel mvps that
I could study to help myself, I sure would appreciate being pointed in
that direction.

As always, thank you for your time and expertise
Joanne

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

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

It's working sweet now - thank you.

On to the next problem ;- )
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

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

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

Ron
That is way too cool. I have never used autofilter before. I think I
need to study up on it.

Before I go any further developing this little app, I would like your
advice on whether I am going about it correctly or if I should come in
from another angle.

I am going to add several more fields (12 maybe) to each record, and
depending on the record info,
I am going to open a copy of the master pricing sheet,
read the record to see which columns I need to show,
write the code to hide the columns I don't need to show,
then show the 'new' ws to the user so they can do their thing

So the next job I have is to first complete my table
Then learn how to cycle thru the record & hide unwanted columns

This should work, shouldn't it?

Again and again, thank you for all of your time and consideration of my
questions. You guys are great! I learn so much from reading thru the
groups and especially when you give my stuff your attention.
Joanne
Ron Coderre wrote:

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

Rather than chop up your workbook, I'd recommend creating a new one (with
vba) and pasting the appropriate records into it.

Something like this variation of my previously posted code:

'---------Start of Code--------
Private Sub cmdPullSelectedData_Click()
Dim wbkNew 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

'Create a new workbook and put the selected record into it
Set wbkNew = Workbooks.Add

'Copy the visible cells from the filtered list
.SpecialCells(xlCellTypeVisible).Copy

With wbkNew.Sheets(1).Range("A1")
'Paste the col widths, values, and formats into the new wkbk
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues

'Turn off copy mode
Application.CutCopyMode = False
End With
'Turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With

End Sub
'---------End of Code--------


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

XL2002, WinXP


"Joanne" wrote:

Ron
That is way too cool. I have never used autofilter before. I think I
need to study up on it.

Before I go any further developing this little app, I would like your
advice on whether I am going about it correctly or if I should come in
from another angle.

I am going to add several more fields (12 maybe) to each record, and
depending on the record info,
I am going to open a copy of the master pricing sheet,
read the record to see which columns I need to show,
write the code to hide the columns I don't need to show,
then show the 'new' ws to the user so they can do their thing

So the next job I have is to first complete my table
Then learn how to cycle thru the record & hide unwanted columns

This should work, shouldn't it?

Again and again, thank you for all of your time and consideration of my
questions. You guys are great! I learn so much from reading thru the
groups and especially when you give my stuff your attention.
Joanne
Ron Coderre wrote:

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

Wow Ron, thank you very much for the great help.

The .PasteSpecial, like AutoFilter, is entirely new to me.

If I understand the code correctly, we are actually creating a new
workbook and putting the record returned from the list box into the
workbook.

What I actually need to do is read the record to identify which wss off
the master price sheet I need to add to the new workbook by comparing
the ws.name on the record to the ws.name in the master wb and then
copy/pastespecial those I need to the new wb.
Then I need to look at the worksheets in the new wb, identify the
columns that I need to show on each of these ws (will always be colA,
colB and colC plus 1 more column per ws), hide the rest of the columns,
and finally show the new wb to the user.

Your help in teaching me how to isolate the record that I need is a
major hurdle to have solved towards this app (and useful for most future
apps too I am sure). Now I need to learn how to read the record to pass
the ws names I need to a function? that will do the copy/paste job. Then
another function to read the record again to see what cols from each ws
in the new wb need to be hidden from view.

I'm thinking something like this might work (though I am really just
stumbling around trying to find my way here) to add the ws to the new
wb:

For each ws.name on record
if ws.name on record then
copy (or .PasteSpecial?) ws.name from master to new wb
end if
next
This needs to compare the record fields to the master wb wss.name
somehow

Then to hide the cols I don't want showing
For each ws.name in new wb
For each colAddr on record
if coladdr not on record then
hide.coladdr
end if
next
next
I'm sure my syntax needs work here but for now I am just trying to think
out the jobs that need to be accomplished.

I have to go thru many file folders to get the ws.names and coladdrs for
each of the customers' records, so I have time to keep pondering these
problems, keep reading the groups, and learn and test as much as I can.

Your help goes a long way in making this feasible for me to finish
Thank you.
Could you recommend a book or helper site to study that doesn't contain
888 bazillion pages? Just a good index and some good examples would be
great and something my schedule could handle.
TIA
Joanne


Ron Coderre wrote:

Rather than chop up your workbook, I'd recommend creating a new one (with
vba) and pasting the appropriate records into it.

Something like this variation of my previously posted code:

'---------Start of Code--------
Private Sub cmdPullSelectedData_Click()
Dim wbkNew 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

'Create a new workbook and put the selected record into it
Set wbkNew = Workbooks.Add

'Copy the visible cells from the filtered list
.SpecialCells(xlCellTypeVisible).Copy

With wbkNew.Sheets(1).Range("A1")
'Paste the col widths, values, and formats into the new wkbk
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues

'Turn off copy mode
Application.CutCopyMode = False
End With
'Turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With

End Sub
'---------End of Code--------


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

XL2002, WinXP


"Joanne" wrote:

Ron
That is way too cool. I have never used autofilter before. I think I
need to study up on it.

Before I go any further developing this little app, I would like your
advice on whether I am going about it correctly or if I should come in
from another angle.

I am going to add several more fields (12 maybe) to each record, and
depending on the record info,
I am going to open a copy of the master pricing sheet,
read the record to see which columns I need to show,
write the code to hide the columns I don't need to show,
then show the 'new' ws to the user so they can do their thing

So the next job I have is to first complete my table
Then learn how to cycle thru the record & hide unwanted columns

This should work, shouldn't it?

Again and again, thank you for all of your time and consideration of my
questions. You guys are great! I learn so much from reading thru the
groups and especially when you give my stuff your attention.
Joanne
Ron Coderre wrote:

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

Hi, Joanne

First, here is a brief sampling of Excel resources that I can recommend:
http://www.contextures.com
http://j-walk.com/ss/excel/tips/index.htm
http://www.cpearson.com/excel/topic.aspx
http://datapigtechnologies.com/ExcelMain.htm

Some of those sites also include lists of good Excel books and links to
other good Excel websites.

Second, you seem to be learning Excel as you build your project. One of the
pitfalls of that process is that you'll tend to try to force a solution using
what you know, instead of using what's most appropriate for the task. To
preserve your sanity, perhaps you could post a synopsis of what data
structures you're working with and what the model needs to do. Then see what
suggestions you get from the talented people in this newsgroup. With one
cohesive plan, you'll end up with an efficient data processing program. The
piecemeal approach usually results in a "camel" (a horse built by a committee)

***********
Regards,
Ron

XL2002, WinXP


"Joanne" wrote:

Wow Ron, thank you very much for the great help.

The .PasteSpecial, like AutoFilter, is entirely new to me.

If I understand the code correctly, we are actually creating a new
workbook and putting the record returned from the list box into the
workbook.

What I actually need to do is read the record to identify which wss off
the master price sheet I need to add to the new workbook by comparing
the ws.name on the record to the ws.name in the master wb and then
copy/pastespecial those I need to the new wb.
Then I need to look at the worksheets in the new wb, identify the
columns that I need to show on each of these ws (will always be colA,
colB and colC plus 1 more column per ws), hide the rest of the columns,
and finally show the new wb to the user.

Your help in teaching me how to isolate the record that I need is a
major hurdle to have solved towards this app (and useful for most future
apps too I am sure). Now I need to learn how to read the record to pass
the ws names I need to a function? that will do the copy/paste job. Then
another function to read the record again to see what cols from each ws
in the new wb need to be hidden from view.

I'm thinking something like this might work (though I am really just
stumbling around trying to find my way here) to add the ws to the new
wb:

For each ws.name on record
if ws.name on record then
copy (or .PasteSpecial?) ws.name from master to new wb
end if
next
This needs to compare the record fields to the master wb wss.name
somehow

Then to hide the cols I don't want showing
For each ws.name in new wb
For each colAddr on record
if coladdr not on record then
hide.coladdr
end if
next
next
I'm sure my syntax needs work here but for now I am just trying to think
out the jobs that need to be accomplished.

I have to go thru many file folders to get the ws.names and coladdrs for
each of the customers' records, so I have time to keep pondering these
problems, keep reading the groups, and learn and test as much as I can.

Your help goes a long way in making this feasible for me to finish
Thank you.
Could you recommend a book or helper site to study that doesn't contain
888 bazillion pages? Just a good index and some good examples would be
great and something my schedule could handle.
TIA
Joanne


Ron Coderre wrote:

Rather than chop up your workbook, I'd recommend creating a new one (with
vba) and pasting the appropriate records into it.

Something like this variation of my previously posted code:

'---------Start of Code--------
Private Sub cmdPullSelectedData_Click()
Dim wbkNew 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

'Create a new workbook and put the selected record into it
Set wbkNew = Workbooks.Add

'Copy the visible cells from the filtered list
.SpecialCells(xlCellTypeVisible).Copy

With wbkNew.Sheets(1).Range("A1")
'Paste the col widths, values, and formats into the new wkbk
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues

'Turn off copy mode
Application.CutCopyMode = False
End With
'Turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With

End Sub
'---------End of Code--------


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

XL2002, WinXP


"Joanne" wrote:

Ron
That is way too cool. I have never used autofilter before. I think I
need to study up on it.

Before I go any further developing this little app, I would like your
advice on whether I am going about it correctly or if I should come in
from another angle.

I am going to add several more fields (12 maybe) to each record, and
depending on the record info,
I am going to open a copy of the master pricing sheet,
read the record to see which columns I need to show,
write the code to hide the columns I don't need to show,
then show the 'new' ws to the user so they can do their thing

So the next job I have is to first complete my table
Then learn how to cycle thru the record & hide unwanted columns

This should work, shouldn't it?

Again and again, thank you for all of your time and consideration of my
questions. You guys are great! I learn so much from reading thru the
groups and especially when you give my stuff your attention.
Joanne
Ron Coderre wrote:

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

Very good advice Ron
I certainly do not want to do the project more than once
I'll post my plan and study up and build my table in the mean time.
Thank you
Ron Coderre wrote:

Hi, Joanne

First, here is a brief sampling of Excel resources that I can recommend:
http://www.contextures.com
http://j-walk.com/ss/excel/tips/index.htm
http://www.cpearson.com/excel/topic.aspx
http://datapigtechnologies.com/ExcelMain.htm

Some of those sites also include lists of good Excel books and links to
other good Excel websites.

Second, you seem to be learning Excel as you build your project. One of the
pitfalls of that process is that you'll tend to try to force a solution using
what you know, instead of using what's most appropriate for the task. To
preserve your sanity, perhaps you could post a synopsis of what data
structures you're working with and what the model needs to do. Then see what
suggestions you get from the talented people in this newsgroup. With one
cohesive plan, you'll end up with an efficient data processing program. The
piecemeal approach usually results in a "camel" (a horse built by a committee)

***********
Regards,
Ron

XL2002, WinXP


"Joanne" wrote:

Wow Ron, thank you very much for the great help.

The .PasteSpecial, like AutoFilter, is entirely new to me.

If I understand the code correctly, we are actually creating a new
workbook and putting the record returned from the list box into the
workbook.

What I actually need to do is read the record to identify which wss off
the master price sheet I need to add to the new workbook by comparing
the ws.name on the record to the ws.name in the master wb and then
copy/pastespecial those I need to the new wb.
Then I need to look at the worksheets in the new wb, identify the
columns that I need to show on each of these ws (will always be colA,
colB and colC plus 1 more column per ws), hide the rest of the columns,
and finally show the new wb to the user.

Your help in teaching me how to isolate the record that I need is a
major hurdle to have solved towards this app (and useful for most future
apps too I am sure). Now I need to learn how to read the record to pass
the ws names I need to a function? that will do the copy/paste job. Then
another function to read the record again to see what cols from each ws
in the new wb need to be hidden from view.

I'm thinking something like this might work (though I am really just
stumbling around trying to find my way here) to add the ws to the new
wb:

For each ws.name on record
if ws.name on record then
copy (or .PasteSpecial?) ws.name from master to new wb
end if
next
This needs to compare the record fields to the master wb wss.name
somehow

Then to hide the cols I don't want showing
For each ws.name in new wb
For each colAddr on record
if coladdr not on record then
hide.coladdr
end if
next
next
I'm sure my syntax needs work here but for now I am just trying to think
out the jobs that need to be accomplished.

I have to go thru many file folders to get the ws.names and coladdrs for
each of the customers' records, so I have time to keep pondering these
problems, keep reading the groups, and learn and test as much as I can.

Your help goes a long way in making this feasible for me to finish
Thank you.
Could you recommend a book or helper site to study that doesn't contain
888 bazillion pages? Just a good index and some good examples would be
great and something my schedule could handle.
TIA
Joanne


Ron Coderre wrote:

Rather than chop up your workbook, I'd recommend creating a new one (with
vba) and pasting the appropriate records into it.

Something like this variation of my previously posted code:

'---------Start of Code--------
Private Sub cmdPullSelectedData_Click()
Dim wbkNew 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

'Create a new workbook and put the selected record into it
Set wbkNew = Workbooks.Add

'Copy the visible cells from the filtered list
.SpecialCells(xlCellTypeVisible).Copy

With wbkNew.Sheets(1).Range("A1")
'Paste the col widths, values, and formats into the new wkbk
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues

'Turn off copy mode
Application.CutCopyMode = False
End With
'Turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With

End Sub
'---------End of Code--------


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

XL2002, WinXP


"Joanne" wrote:

Ron
That is way too cool. I have never used autofilter before. I think I
need to study up on it.

Before I go any further developing this little app, I would like your
advice on whether I am going about it correctly or if I should come in
from another angle.

I am going to add several more fields (12 maybe) to each record, and
depending on the record info,
I am going to open a copy of the master pricing sheet,
read the record to see which columns I need to show,
write the code to hide the columns I don't need to show,
then show the 'new' ws to the user so they can do their thing

So the next job I have is to first complete my table
Then learn how to cycle thru the record & hide unwanted columns

This should work, shouldn't it?

Again and again, thank you for all of your time and consideration of my
questions. You guys are great! I learn so much from reading thru the
groups and especially when you give my stuff your attention.
Joanne
Ron Coderre wrote:

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