Populate userform from row values based on combobox

**robertogavilan** · 07-20-2014, 05:16 PM

hi,

how can I build a userform to display all values from a row? in order to select a row, i was thinking to use a combobox to display values from one column and based on that selection, display all values in that row? the reason i want to use a userform is because i would also like to be able to edit that data. Any help would be much appreciated!!!!

thanks.

**stnkynts** · 07-20-2014, 05:38 PM

So you have a UserForm, lets call it UserForm1, and in it you have a combobox, lets call it combobox1, which will display all values from a column, in our case we will assume column A. You want to select a value in combobox1 and have it display all the values in that row in a listbox, lets call it listbox1. All of this will be occuring based upon the values in a worksheet which we will call "Sheet1". Here is code:

Private Sub ComboBox1_Change()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim rFind As Range, rCol As Range
Dim LC As Long

Set rFind = ws.Range("A1:A" & ws.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)

If Not rFind Is Nothing Then
    LC = ws.Cells(rFind.Row, Columns.Count).End(xlToLeft).Column
    For Each rCol In ws.Range(ws.Cells(rFind.Row, 2), ws.Cells(rFind.Row, LC))
        If Not IsEmpty(rCol) Then
            With ListBox1
                .AddItem rCol.Value
            End With
        End If
    Next rCol
End If

End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim rCell As Range

If ws.Range("A" & Rows.Count).End(xlUp).Row = 1 And ws.Range("A1").Value = "" Then
    MsgBox ("There is no data to populate combobox1.")
    Exit Sub
End If

For Each rCell In ws.Range("A1:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)
    If Not IsEmpty(rCell) Then
        With ComboBox1
            .AddItem rCell.Value
        End With
    End If
Next rCell

End Sub

**robertogavilan** · 07-20-2014, 06:04 PM

Thank you so much!

If I wanted to have each value displayed on a texbox, how would I have to do it?

**mikerickson** · 07-20-2014, 06:32 PM

This uses a multi column listbox rather than VLookup. As written, it only deals with 3 columns of data. If you need more you should change the RangeOfData function, and add more TextBoxes

Dim ufEventsDisabled As Boolean

Private Sub ListBox1_Click()
    If ufEventsDisabled Then Exit Sub
    With ListBox1
        If .ListIndex <> -1 Then
            ufEventsDisabled = True
            Me.TextBox1 = .List(.ListIndex, 0)
            Me.TextBox2 = .List(.ListIndex, 1)
            Me.TextBox3 = .List(.ListIndex, 2)
            Me.CommandButton2.Enabled = False
            ufEventsDisabled = False
        End If
    End With
End Sub

Private Sub CommandButton2_Click()
    Rem apply changes
    If ufEventsDisabled Then Exit Sub
    With Me.ListBox1
        If .ListIndex <> -1 Then
            ufEventsDisabled = True
            .List(.ListIndex, 0) = Me.TextBox1.Text
            .List(.ListIndex, 1) = Me.TextBox2.Text
            .List(.ListIndex, 2) = Me.TextBox3.Text
            ufEventsDisabled = False
            RangeOfData.Cells(.ListIndex + 1, 1) = Me.TextBox1.Text
            RangeOfData.Cells(.ListIndex + 1, 2) = Me.TextBox2.Text
            RangeOfData.Cells(.ListIndex + 1, 3) = Me.TextBox3.Text
            Me.CommandButton2.Enabled = False
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = RangeOfData.Columns.Count
        .List = RangeOfData.Value
        .ColumnWidths = Application.WorksheetFunction.Rept(";0", .ColumnCount)
    End With
    Me.CommandButton2.Enabled = False
End Sub

Function RangeOfData() As Range
    With Sheet1.Range("A:A")
        Set RangeOfData = Range(.Cells(2, 3), .Cells(Rows.Count, 1).End(xlUp))
    End With
End Function

Private Sub TextBox1_Change()
    If ufEventsDisabled Then Exit Sub
    Me.CommandButton2.Enabled = True
End Sub
Private Sub TextBox2_Change()
    If ufEventsDisabled Then Exit Sub
    Me.CommandButton2.Enabled = True
End Sub
Private Sub TextBox3_Change()
    If ufEventsDisabled Then Exit Sub
    Me.CommandButton2.Enabled = True
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub