Userform listbox (multiple cols) to edit values and update sheet & list

Question

I have a userform in Excel which has a listbox which pulls data from a dynamic range (5 columns, 1 being a unique ID number).

When the user clicks the record it displays each value in a text box and the ID number in a label.

Private Sub ListBox1_Click()
  Me.Label6 = Me.ListBox1.List(ListBox1.ListIndex, 0) 'record number
  Me.TextBox1 = Me.ListBox1.List(ListBox1.ListIndex, 4)'Staff name
  Me.TextBox2 = Me.ListBox1.List(ListBox1.ListIndex, 1)'Strategy
  Me.TextBox3 = Me.ListBox1.List(ListBox1.ListIndex, 2)'Notes
  Me.TextBox4 = Me.ListBox1.List(ListBox1.ListIndex, 3)'Date
End Sub

I want the user to makes changes to the record and update the list box and data on sheet. It is updating the right record just with one value!

User form with updated same value in all columns see rec# 5

Private Sub CMDUpdate_Click()

Dim erowa As Integer
Dim x  As Integer

erowa = Application.WorksheetFunction.CountA(Sheet9.Range("A:A"))

For x = 2 To erowa

   If Sheet9.Cells(x, "A").Value = Me.Label6 Then  'this is the unique ID

       Sheet9.Cells(x, "B").Value = Me.TextBox1.Value 'Staff name
       Sheet9.Cells(x, "C").Value = Me.TextBox2.Value 'Strategy
       Sheet9.Cells(x, "D").Value = Me.TextBox3.Value 'Notes 
       Sheet9.Cells(x, "E").Value = Me.TextBox4.Value 'date

   End If
Next

End Sub

Also date format for some reason displays the Julian value!

I spent a whole day researching, watching tutorials, reading blogs.

your code is updating the whole range with the current values in the text boxes. You'd need to look for the record and update is accordingly — Ricardo Diaz
Oh OK well that makes sense thank you! Do you have any suggestions on what I code I could use? — evilemma2001

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2020-01-30T20:28:46

This is a way of finding the value in Me.Label6 and updating the cells next to it

Private Sub CMDUpdate_Click()

    Dim cell As Range

    Set cell = sheet9.Range("A:A").Find(What:=Me.Label6.Caption, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    MatchCase:=False)

    If Not cell Is Nothing Then

        ' Column B
        cell.Offset(, 1).Value = Me.TextBox1.Value 'Staff name
        ' Column C
        cell.Offset(, 2).Value = Me.TextBox2.Value 'Strategy
        ' Column D
        cell.Offset(, 3).Value = Me.TextBox3.Value 'Notes
        ' Column E
        cell.Offset(, 4).Value = Me.TextBox4.Value 'date

    Else

        MsgBox "Record: " & Me.Label6.Caption & " not found in column A!"

    End If

End Sub

Let me know if it works!

Userform listbox (multiple cols) to edit values and update sheet & list

1 Answers