vlookups in userform - selections to populate other fields

[Newport Count]

Hi

I am trying to to use vlookup on a userform so that when a value is entered in comboboxA, textbox1 is filled with data from another worksheet - is this possible?

many thanks

 

[Norie]

Yes but you can probably do it without a lookup.

How has the combobox been populated and how is it related to the data on the other worksheet?

 

[Newport Count]

Thanks Norie,

the combobox (cboCompanyName) is populated from a list of names in another worksheet as follows:

Private Sub UserForm_Initialize()
 
    Dim cCompanyName As Range
 
    Dim ws As Worksheet
    Set ws = Worksheets("DropDowns")
 
    For Each cCompanyName In ws.Range("CompanyName")
    With Me.cboCompanyName
     .AddItem cCompanyName.Value
    End With
    Next cCompanyName

the above populates the dropdown for company name - then based on what is chosen in company name determines the next dropdown for company contact (cboContactName) as follows:

Private Sub cboCompanyName_Change()
    Dim rngCompany As Range
    Dim rngList As Range
    Dim strSelected As String
    Dim LastRow As Long
 
      If cboCompanyName.ListIndex <> -1 Then
 
           strSelected = cboCompanyName.Value
 
           LastRow = Worksheets("DropDowns").Range("A" & Rows.Count).End(xlUp).Row
 
           Set rngList = Worksheets("DropDowns").Range("A2:A" & LastRow)
           cboContactName.Clear
 
            For Each rngCompany In rngList
                 If rngCompany.Value = strSelected Then
                      cboContactName.AddItem rngCompany.Offset(, 1)
                 End If
           Next rngCompany
 
      End If

in "DropDowns" i have 4 columns - columnA displays the company name, columnB displays a customer contact name, columnC displays the contact telephone number and columnD displays the contact email address.

There can be duplicate values for company name in Column A but all other values are unique - the 4 columns are sorted alphabetically in order of company name, then contact name

I have 2 text boxes in my userform textboxTel & textboxEmail - i require these 2 textboxes to display the chosen contact name's telephone number and email address if it is present in "DropDowns"

many many thanks as always

 

[Newport Count]

I have found the solution to this:

Private Sub cboContactName_Change()
    Dim ws  As Worksheet
    Dim tmp As Range
    
    Set ws = Worksheets("DropDowns")
    Set tmp = ws.Range("B2:D201")
    txtContactTel.Value = WorksheetFunction.VLookup(cboContactName.Value, tmp, 2, False)

End Sub

i tried to find similar posts and managed to work it out

Cheers!

 

[Norie]

I was going to suggest using Find but looks like you've found the solution.

 

[Newport Count]

Ok back again!

the form works how i would like it but if the form is reset then i get the following error:

Run-time error '1004':
Unable to get the VLookup property of the WorksheetFunction class

the same error appears when i switch to a different company name, or if there is no contact name that defaults (so one can be typed).

must be still trying to find data that is not there in the lookup

any ideas? thanks in advance

 

[Norie]

What's probably happening is that when you do change the value returned by cboContactName.Value is "".

You can get round that with various methods, the simplest being to check that the value isn't "".

If cboContactName.Value <> "" And cboContactName.ListIndex <> -1 Then
 ... code for lookup
End If

 

[mikerickson]

Another approach would be to load the VLookedUp values into cboContactName

cboContactName.Clear
cboContactName.ColumnCount = 3
cboContatctName.ColumnWidths = ";0;0"

For Each rngCompany In rngList
    If rngCompany.Value = strSelected Then
        With cboContactName
            .AddItem rngCompany.Offset(, 1)
            .List(.ListCount - 1, 1) = rngCompany.Offset(0,2)
            .List(.ListCount - 1, 2) = rngCompany.Offset(0,3)
        End With
    End If
Next rngCompany

Then selecting a contactname would work like

Private Sub cboContactName_Change()
    With cboContactName
        If .ListIndex <> - 1 Then
            txtContactTel.Text = .List(.ListIndex, 1)
            txtEMail.Text = .List(.ListIndex, 2)
        Else
            txtContactTel.Text = vbNullString
            txtEMail.Text = vbNullString
        End If
    End With
End Sub

 

[Norie]

Mike

That's a great idea - I've never really thought of doing that with a userform in Excel but it's kind of the 'norm' to do in Access.

I was going to suggest something similar, add one extra column and populate it with the row of the contact.

That could still be added and might be useful for other things, eg editing contact details.

 

[mikerickson]

Mike

That's a great idea - I've never really thought of doing that with a userform in Excel but it's kind of the 'norm' to do in Access.

I was going to suggest something similar, add one extra column and populate it with the row of the contact.

That could still be added and might be useful for other things, eg editing contact details.

For editiing back to the worksheet, I'd add another hidden column for rngCompany.Address(,,,True) to find the proper cells to edit.