none
Filling UserForm Based On DropDown List RRS feed

  • Question

  • Hi All

    I'm trying to fill out a user form using the client code dropdown list.

    I want it to take the value from the drop down list and fill out textbox1 with corresponding value in column B from sheet2 PivotTable2 - 2 columns (column A = client code, column B = value). I also would like it to take data from another pivot table to fill out textbox2, same layout as before sheet2 PivotTable3 only column D = client code, column E = no. of items. Finally it needs to fill in the textbox3 next to outstanding value with the correct currency which again can be searched based on client code. Client code for currency is in column G and corresponding currency in column H.

    Client Code = ComboBox1

    Outstanding Value = TextBox1

    Currency = TextBox3

    No. of Items = TextBox2

    Thank You in Advance

    Monday, March 9, 2015 10:58 AM

All replies

  • Try this, substituting the correct names for the controls and for the sheet:

    Private Sub ComboBox1_AfterUpdate()
        Me.TextBox1 = Application.VLookup(Me.ComboBox1, Worksheets("Sheet2").Range("A:B"), 2, False)
        Me.TextBox2 = Application.VLookup(Me.ComboBox1, Worksheets("Sheet2").Range("D:E"), 2, False)
        Me.TextBox3 = Application.VLookup(Me.ComboBox1, Worksheets("Sheet2").Range("G:H"), 2, False)
    End Sub

    You can replace the references to entire columns such as A:B to finite ranges such as A2:B10 if you wish.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 14, 2015 12:17 PM
  • That's brilliant thank you; just one small problem what do you do for errors/ missing data; for instance there's a value in combobox1 that cannot be found and at the moment its coming up with

    run time error '-2147352571 (80020005)': Could not set the value property. type mismatch.

    is there a piece of code that is equivalent to the "if error leave blank" as per the below formula?

    =iferror(vlookup(a2,a:b,2,0),"")

    Thanks


    Red Hood - Y.O.L.T

    Monday, March 16, 2015 9:44 AM
  • Try this:

    Private Sub ComboBox1_AfterUpdate()
        Dim v As Variant
        v = Application.VLookup(Me.ComboBox1, Worksheets("Sheet2").Range("A:B"), 2, False)
        If IsError(v) Then
            Me.TextBox1 = ""
        Else
            Me.TextBox1 = v
        End If
        v = Application.VLookup(Me.ComboBox1, Worksheets("Sheet2").Range("D:E"), 2, False)
        If IsError(v) Then
            Me.TextBox2 = ""
        Else
            Me.TextBox2 = v
        End If
        v = Application.VLookup(Me.ComboBox1, Worksheets("Sheet2").Range("G:H"), 2, False)
        If IsError(v) Then
            Me.TextBox3 = ""
        Else
            Me.TextBox3 = v
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, March 16, 2015 3:43 PM