Using dropdown list to pull data from another workbook

[3kidsgrayhair]

Hello, I am a new member getting my feet wet with Macros. I have used the board to find some help but am having trouble with this problem.

1) Problem. I have two workbooks, one with customer info ("Customers") and another we are using to create and track projects ("T&M Project Sheet2"). In the T&M workbook I have included a drop down list from the forms toolbar listbox that lists Customers from the Customers workbook. When the user selects the Customer in the dropdown, I would like to draw more information about the customer (address, phone, etc) to populate specific cells on the T&M workbook.

2) Efforts. I can get the list to output the row number of the selected customer to a cell. I used the Macro recorder to copy and paste data, but it uses the specific row number I recorded, not the variable based on the customer selection. An abbreviated version of the code is below:

Sub customerfill()
'
' customerfill Macro
' copy and paste customer info
'

'
Application.ScreenUpdating = False
Windows("customers.xls").Activate
ActiveWindow.SmallScroll Down:=48
Windows("T&M Project Sheet2.xls").Activate
Windows("customers.xls").Activate
Range("D100").Select
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O13").Select
ActiveSheet.Paste
Windows("customers.xls").Activate
Range("B100").Select
Application.CutCopyMode = False
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O14").Select
ActiveSheet.Paste
End Sub

3) Questions. Can I replace the recorded row (100) with a variable based on the drop down list (i.e. 245)? If so, how do I do that? Is there a better way to do this?

Thanks so much for your help.

 

[d0wnt0wn]

what I would do is use a vlookup fomula for this instead of the macro... lets say your drop down list is in a1 on your t&m sheet and your list comes from a range on your customers sheet in the "A" column. To the right of your names in customers sheet you have columns with their info.... column b is phone number, column c is address...... now go like this

on your t&m sheet in b1 typ a fomula similar to this...

=IF(ISNA(VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,2,FALSE)),"",VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,2,FALSE))

(the path of the file will be different but I hope you get the idea)

now in c1 on your t&m sheet put this formula.... notice i changed the 2 to a 3 in both sections of formula

=IF(ISNA(VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,3,FALSE)),"",VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,3,FALSE))

now if you select one of the names in your drop down the other 2 cells next to it fill in automatically


how it works:

$a$1 is the cell that the formula is looking for a data change in (dropdown box)

$A$1:$C$100 is the range on your customer sheet that the formula looks in once you select a name in the dropdown


2 or 3 is the column number to copy the info from.

Hope this was helpful

 

[Tazguy37]

Something like this could go in your ComboBox1_Change() module:

Did you say you used a listbox instead? The code would be similar. I used a ComboBox because I figured you would only want one selected at a time. Post back if that's a problem.

Private Sub ComboBox1_Change()
Dim cell As Range

    For Each cell In Sheets("Customers").Range("CustomerList")
        If cell.Value = ComboBox1.Value Then
            Sheets("T&M Project Sheet2").Range("B2").Value = cell.Offset(0, 1)
            Sheets("T&M Project Sheet2").Range("C2").Value = cell.Offset(0, 2)
        End If
    Next cell
    
End Sub

It's not the nicest code in the world, but it gets the job done. You may have to adjust your ranges some. I created a range "CustomerList" on the sheet "Customers" that consists of all the customer names, but you could use cell references instead if you prefer. Hope this helps!

Here's what my sheets look like, by the way:
Customers sheet

Book2
	A	B	C	D
1	CustomerName	Address	Phone
2	CustomerA	AddressA	PhoneA
3	CustomerB	AddressB	PhoneB
4	CustomerC	AddressC	PhoneC
5	CustomerD	AddressD	PhoneD
6	CustomerE	AddressE	PhoneE
7	CustomerF	AddressF	PhoneF
Customers

T&M Project Sheet2 (The combo box over cell A2 won't be visible:

Book2
	A	B	C	D
1	CustomerName	Address	Phone
2		AddressF	PhoneF
T&M Project Sheet2

 

[HalfAce]

Taz,
The "grey haired one" (and believe me, I'm not one to speak! ) is pulling the customer info from a different workbook altogether.

I would go with d0wnt0wn's idea, but instead of using the listbox from the Forms toolbar, you might look at using an ActiveX listbox (from the Controls Toolbox toolbar). This will show you the actual text of your selection instead of just the index number of it in your list.

Dan

 

[Tazguy37]

I see that now. Still, a Workbook object could be added to the Sheets property in the code I posted. I agree that d0wnt0wn's solution is probably simpler to implement, but who am I to argue with the OP?

 

[3kidsgrayhair]

Thanks jfor all the valuable input. I will try these suggestions tomorrow when I get back to my task.