CODEWITHSUNDEEP
excelvbacomboboxtextbox
J.Doe
J.Doe

Reputation: 33

Return value in text box from Combobox in Excel

I'm just looking for the most straight forward way to return a value in three text boxes, based off a selection from a combobox within a Userform.

I have a Worksheet called 'TPIDLookup' which comprises of 4 columns; TPID, Branch Plant, Company No and MQ Name. I have set ranges for all four columns, now I just need the values from Branch Plant, Company and MQ Name to be displayed when a TPID is selected from the Combobox. I'm lost when it comes to doing this within Excel however, although I'm familiar with how it would be done within Access.

If it helps, here is the code I've used to generate the combobox list:

Set WS = Worksheets("TPIDLookup")
For Each TPID In WS.Range("TPID")
With Me.cmbTPID
.AddItem TPID.Value
End With
Next TPID

My research over the web has confused me even more, and I'm certain there is a dead straight forward way to do this. I am not a programmer and I am new to VBA, but any help would be greatly appreciated as this is the last step for me to complete my form.

Many Thanks,

John.

Upvotes: 0

Views: 2521

Answers (2)

user3598756
user3598756

Reputation: 29421

you could try this

here's your "main" sub code excerpt relevant for your question

Option Explicit

Public TPIDRng As Range, branchRng As Range, companyRng As Range, MQRng As Range

Sub main()

Set TPIDRng = GetColumnUsedRange(Worksheets("TPIDLookup"), "A")
Set branchRng = GetColumnUsedRange(Worksheets("TPIDLookup"), "B")
Set companyRng = GetColumnUsedRange(Worksheets("TPIDLookup"), "C")
Set MQRng = GetColumnUsedRange(Worksheets("TPIDLookup"), "D")

With UserForm1 ' change it to whatever name your actual UserForm has
    .ComboBox1.List = Application.Transpose(TPIDRng)
    .Show
End With
Unload UserForm1

End Sub

Function GetColumnUsedRange(sht As Worksheet, col As String)

With sht
    Set GetColumnUsedRange = .Range(.Cells(1, col), .Cells(.Rows.Count, col).End(xlUp))
End With
End Function

and here's the corresponding code in the UserForm code pane

Private Sub ComboBox1_Change()

With Me
    If .ComboBox1.ListIndex = -1 Then
        .TextBoxBranch.Text = ""
        .TextBoxCompany.Text = ""
        .TextBoxMQ.Text = ""
    Else
        .TextBoxBranch.Text = branchRng.Cells(.ComboBox1.ListIndex + 1, 1).Value
        .TextBoxCompany.Text = companyRng.Cells(.ComboBox1.ListIndex + 1, 1).Value
        .TextBoxMQ.Text = MQRng.Cells(.ComboBox1.ListIndex + 1, 1).Value
    End If
End With

End Sub

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

change names (for ranges, Userform, TextBoxes and Combobox) as per your needs

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

Create a combobox change event for your cmbTPID combobox and use this code. Replace textbox names and named range names with your actual names:

Private Sub cmbTPID_Change()

    Dim ws As Worksheet
    Dim rFound As Range

    If Me.cmbTPID.ListIndex = -1 Then
        'Nothing selected, or user manually typed an invalid entry
        'Clear textboxes
        Me.txtBranch.Value = vbNullString
        Me.txtCompanyNo.Value = vbNullString
        Me.txtMQName.Value = vbNullString
    Else
        'Valid TPID selected
        'Find it and populate the textboxes
        Set ws = ActiveWorkbook.Sheets("TPIDLookup")
        Set rFound = ws.Range("TPID").Find(Me.cmbTPID.Text, , xlValues, xlWhole)
        If Not rFound Is Nothing Then
            Me.txtBranch.Value = Intersect(ws.Range("Branch"), rFound.EntireRow).Value
            Me.txtCompanyNo.Value = Intersect(ws.Range("CompanyNo"), rFound.EntireRow).Value
            Me.txtMQName.Value = Intersect(ws.Range("MQName"), rFound.EntireRow).Value
        End If
    End If

End Sub

Upvotes: 1

Related Questions