Userform Listbox updating and adding values using Textbox

[zubin]

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:
    'http://stackoverflow.com/questions/10763310/how-to-populate-data-from-a-range-multiple-rows-and-columns-to-listbox-with-vb
    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)
            Next
            rw = rw + 1
        Next


        .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
    Else
        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
            'http://stackoverflow.com/questions/23050647/find-a-row-of-the-selected-item-in-listbox-vba
            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