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