Combo Box

[bobwilson]

How do I write a Macro under a Combo Box that will update information on
the current sheet based on the item selected from the list?

What I'm trying to do is Select a Name from the Combo Box. When
selecting the Name I want it to populate the persons address, phone,
fax and email. This information is pulled from another sheet (Sheet2)
where the data is in rows. Address, Phone, Fax & email are all
different cell on both sheets.

 

[Rick Hansen]

Bob, What is the layout of each sheet, ie sheet1 & sheet2. It sounds like
simple search with some copy and paste from each sheet. Please give me
alittle bit more info. I believe we can come up with a soluation.

Rick

 

[bobwilson]

I have cells with Name, Phone, Fax, Email etc. on the ActiveSheet. I use
a macro to push the data entered on to Customer List.

My problem is that I use the same form to Push(add record to my list)
Data to the Sheet and Pull(retrieve record from list) Data. I'm
currently using vlookup to pull the data however since I want to also
push data, it over writes the formulas. I want to use the Combo Box to
select the person from the list and populate the Name, Phone, Fax,
Email etc. cells (They are no in one row or column) on the active
sheet. On the record sheet(Customer List) the data is in one row.


I was trying the following code but it fails. I have noted where it is
happening below.

Thanks for your help

Sub custlistcombobox()

Dim FirstAddress As String
Dim c As Range

With Worksheets("Customer List").Range("B2:B15501")
Set c = .Find(custlistcombobox.Value, LookIn:=xlValues) '**** Failing
here***

If Not c Is Nothing Then
FirstAddress = c.Address
Do
With ActiveSheet.Range("F105")
' Return data to a static row - My Data is not static Row FYI.
Value = c ' Name
Offset(1, -3) = c.Offset(, 1) ' Phone
Offset(1, 0) = c.Offset(, 2) ' Fax
Offset(2, -3) = c.Offset(, 3) ' Email
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

 

[Rick Hansen]

Bob, Is you combobox run from a UserForm, our is a Control on the
worksheet?

Rick

 

[bobwilson]

User Form Button not Control

 

[Rick Hansen]

Bob, Thank you for info. I beleive one of your problem
"custlistcombobox.Value" can't be found in your subroutine. when using the
"find method". You'll probably have to pass this custlistcombobox.Value
from your Userform. I believe you using a commandButton to call you
subroutine of custlistcombobox()

suggested changes :

Sub custlistcombobox(Customer as String) '' Change Sub header to
accept string varible

Set c = srcRng.Find( what:=Customer, LookIn:=xlValues)

try this changes.

Rick

 

[bobwilson]

I'm unclear on your solution.

I'm using the forms button and not the contol toolbox.

 

[bobwilson]

I could search for the value in the ComboBox's Linked cell. I can se
that working. I'm new to VB so if you could help me out with some cod
to call the data from my record sheet based on the outputed value fro
the linked cell, I can modify it for my need

 

[Rick Hansen]

Hey , Bob, Your right on with your last comment. I've modified with the
correct changes and have tested it also. It works great . See the code
below. Good Luck...

Rick , Fbks, AK

Sub custlistcombobox()

Dim FirstAddress As String
Dim c As Range

With Worksheets("Customer List").Range("B2:B15501")
'-> Range("J1") is the linkcell for the "Forms Combo Box"
'-> change this to match you code
Set c = .Find(what:=Range("J1"), LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
With ActiveSheet.Range("F105")

' Return data to a static row - My Data is not static Row FYI.
.Value = c ' Name
.Offset(1, -3) = c.Offset(, 1) ' Phone
.Offset(1, 0) = c.Offset(, 2) ' Fax
.Offset(2, -3) = c.Offset(, 3) ' Email
End With
Set c = .FindNext(c)
'''Set c = srcRng.FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

End Sub

 

[bobwilson]

Rick,

Thanks for the code fix....works great,

Bo