Results 1 to 1 of 1

Thread: Userform Listbox updating and adding values using Textbox
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Userform Listbox updating and adding values using Textbox

    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 Fluff; Sep 15th, 2019 at 10:18 AM. Reason: Corrected code tags

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •