Using Contents of ComboBox to Populate other fields

[vastyle]

Hi Guys,

I have a problem, I have a worksheet that basically has contact info of individuals. like company name (1 clol) First Name (2 col), Last (3 col) etc.

I have used company name column to populate a combobox. wat i am tryin 2 achieve is, when i select a company from d drop down menu, and click on a command button called View Contact. I want to be able to find the company name on d worksheet under d company name column, then using that information (i.e row number) to populate all the other textbox fields (first name, last name, etc) which are basically Offsets (0,1..etc) of d same row.

Anyone out there have an idea how to do this

 

[johnny45]

Try this,
I found origional source here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=600

Sub Get_vals

 Dim Employee As Variant
    Dim Name As String
    Dim firstaddress As String

    Employee = Empty
     'If you add more than 500 names you will need to increase this
    With Sheet1.Range("A:A")
        Name = no
        Set Employee = .Find(What:=Name, LookIn:=xlValues, LookAt:= _
       xlWhole)
'        If Not Employee Is Nothing Then  Else: Exit Sub

        If Not Employee Is Nothing Then
'''        MsgBox Employee.Row
        lines.Label1.Caption = Sheet1.Cells(Employee.Row, 2)
       etc.....
        End If
End sub

 

[johnny45]

PS I'm building a similar project, found this to be pretty cool also:

http://www.dicks-blog.com/archives/2005/02/17/limit-a-listbox-a-different-view/

 

[vastyle]

Thanks Guys would try it out and let u know how it goes

 

[SkipVought]

Hi,

I use MS Query often to return a list based on a criteria selected from a ComboBox control.

faq68-5829

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Rofeu]

You can even do this with just simple formulae.

Link your combobox to cell A1 and then just put e.g. vlookups [ =IF(ISNA(VLOOKUP()),"",VLOOKUP()) ]in B1, C1, etc and link those cells to your textboxes.

Cheers,

Roel