Userform Listbox updating and adding values using Textbox


New Member
Sep 15, 2019
Good evening,
Can some one please guide me, am stuck with the adding and updating of the list box...
My userform contains a list box which is populated by a range from a current worksheet ie the last worksheet of the book, I need only three columns to be shown in the list box, column"Q", "R" and "S" titled as "SCMCode" ,"BrandName" and "Size" respectively, also having a text box (14, 15 ,16) showing the values when selected in the listbox, These values have to be added in the text box only when adding a new entry using the "ADD" command button, but otherwise they will show the default values and not to be edited when using the "UpDate" button.
There are 13 textboxes in the userform which display the corresponding values in the rows of the above mentioned columns. these values have to be updated using the "update" command button, and also when a new entry has to be made using the "Add" command button.
Now the problem is the "add" and "Update" codes, which i cannot get it to work.
Please Help.
Option Explicit

Private Sub Label21_Click()

End Sub

Private Sub UserForm_Initialize()

    cmdUpdate.Enabled = False 'Only enable the button when a row has been returned
    'Source for this nifty code is from here:
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String
    Set rng = Range("ListOfData")

    With Me.ListOfData
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count
        .ColumnCount = 3
        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            rw = rw + 1

        .List = Myarray
        '.TopIndex = 1
    End With
    If Val(Me.txtLBSelectionIndex) > 1 Then
        Me.ListOfData.Selected(Val(Me.txtLBSelectionIndex)) = True
    End If

End Sub
Private Sub cmdNewEntry_Click()

    Dim lastrow As Long
    lastrow = ActiveSheet.Range("Q" & Rows.Count).End(xlUp).Row
    Cells(lastrow + 1, "W").Value = TextBox1.Text
    Cells(lastrow + 1, "V").Value = TextBox2.Text
    Cells(lastrow + 1, "BC").Value = TextBox3.Text
    Cells(lastrow + 1, "AX").Value = TextBox4.Text
    Cells(lastrow + 1, "X").Value = TextBox5.Text
    Cells(lastrow + 1, "Y").Value = TextBox6.Text
    Cells(lastrow + 1, "Z").Value = TextBox7.Text
    Cells(lastrow + 1, "AB").Value = TextBox8.Text
    Cells(lastrow + 1, "AE").Value = TextBox9.Text
    Cells(lastrow + 1, "AG").Value = TextBox10.Text
    Cells(lastrow + 1, "AJ").Value = TextBox11.Text
    Cells(lastrow + 1, "AK").Value = TextBox12.Text
    Cells(lastrow + 1, "AO").Value = TextBox13.Text
End Sub

Private Sub ListofData_Click()
    Dim rngMyData As Range

    TextBox1.Value = Me.ListOfData.Column(6)
    TextBox2.Value = Me.ListOfData.Column(5)
    TextBox3.Value = Me.ListOfData.Column(38)
    TextBox4.Value = Me.ListOfData.Column(33)
    TextBox5.Value = Me.ListOfData.Column(7)
    TextBox6.Value = Me.ListOfData.Column(8)
    TextBox7.Value = Me.ListOfData.Column(9)
    TextBox8.Value = Me.ListOfData.Column(11)
    TextBox9.Value = Me.ListOfData.Column(14)
    TextBox10.Value = Me.ListOfData.Column(16)
    TextBox11.Value = Me.ListOfData.Column(19)
    TextBox12.Value = Me.ListOfData.Column(20)
    TextBox13.Value = Me.ListOfData.Column(24)
    Set rngMyData = ActiveSheet.Columns("Q")
    On Error Resume Next
        txtRowNumber = Application.WorksheetFunction.Match(TextBox1.Value, rngMyData, 0)
    On Error Resume Next
    If Val(txtRowNumber) > 1 Then 'Exclude the ability to change the header row.
        cmdUpdate.Enabled = True 'OK to enable the button as an applicable row number has been returned
    End If
End Sub
Private Sub cmdUpdate_Click()

    Dim lngMyRow As Long
    Dim r As Long
    lngMyRow = Val(txtRowNumber)
    If lngMyRow = 0 Then
        MsgBox "Update is not available as a row number for the selected issue could not be found.", vbExclamation
        Exit Sub
        Application.EnableEvents = False
            'Return the selected index number of the selected record in the 'ListOfDetails' listbox so it can be re-selected after the list has been refreshed
            For r = 0 To Me.ListOfData.ListCount - 1
                If Me.ListOfData.Selected(r) Then
                    Me.txtLBSelectionIndex = r
                    Exit For
                End If
            Next r
            'Populate the corresponding cells with any change
            Cells(lngMyRow, "W").Value = TextBox1.Text
            Cells(lngMyRow, "V").Value = TextBox2.Text
            Cells(lngMyRow, "BC").Value = TextBox3.Text
            Cells(lngMyRow, "AX").Value = TextBox4.Text
            Cells(lngMyRow, "X").Value = TextBox5.Text
            Cells(lngMyRow, "Y").Value = TextBox6.Text
            Cells(lngMyRow, "Z").Value = TextBox7.Text
            Cells(lngMyRow, "AB").Value = TextBox8.Text
            Cells(lngMyRow, "AE").Value = TextBox9.Text
            Cells(lngMyRow, "AG").Value = TextBox10.Text
            Cells(lngMyRow, "AJ").Value = TextBox11.Text
            Cells(lngMyRow, "AK").Value = TextBox12.Text
            Cells(lngMyRow, "AO").Value = TextBox13.Text
        Application.EnableEvents = True
    End If
    'Refresh the list
    Me.ListOfData.RowSource = "ListOfData"
    Call UserForm_Initialize

End Sub
Last edited by a moderator:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Latest member