Userform Listbox updating and adding values using Textbox

zubin

New Member
Joined
Sep 15, 2019
Messages
47
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.
Code:
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
 
Last edited by a moderator:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top