How to Update Entry In User Form?

Question

I created a user entry form, that takes data in a table and displays that as a listbox in a VBA form. I've two toggle buttons, that cycle through the data. I've textboxes/combo boxes displaying that data on the form.

I am having trouble making the form update the data. I managed to get the first column to update when data is changed in the form, but I can't get anything else working. So mainly the code in the cmdUpdate_Click sub.

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub cmdRefresh_Click()

    Dim x As Integer

    For x = 0 To Me.lstMyData.ListCount - 1
        If Me.lstMyData.Selected(x) Then Me.lstMyData.Selected(x) = False
    Next x

End Sub

Private Sub ComboBox1_DropButtonClick()
    microParaNameButt Me.ComboBox1
End Sub

'Private Sub ComboBox1_Change()
'    microParaName Me.ComboBox1
'End Sub

Private Sub lstMyData_Click()

    Dim i As Integer

    i = Me.lstMyData.ListIndex
    Me.lstMyData.Selected(i) = True
    
    Me.TextBox1.Value = Me.lstMyData.Column(0, i)
    Me.ComboBox1.Value = Me.lstMyData.Column(0, i)
    
    Me.TextBox2.Value = Me.lstMyData.Column(1, i)
    Me.ComboBox2.Value = Me.lstMyData.Column(1, i)
    
    Me.TextBox3.Value = Me.lstMyData.Column(2, i)
    Me.ComboBox3.Value = Me.lstMyData.Column(2, i)
    
    Me.TextBox4.Value = Me.lstMyData.Column(3, i)
    Me.ComboBox4.Value = Me.lstMyData.Column(3, i)
    
    Me.TextBox5.Value = Me.lstMyData.Column(4, i)
    Me.ComboBox5.Value = Me.lstMyData.Column(4, i)
    
    Me.TextBox6.Value = Me.lstMyData.Column(5, i)
    Me.ComboBox6.Value = Me.lstMyData.Column(5, i)
    
    Me.TextBox7.Value = Me.lstMyData.Column(6, i)
    Me.ComboBox7.Value = Me.lstMyData.Column(6, i)
    
    Me.TextBox8.Value = Me.lstMyData.Column(7, i)
    Me.ComboBox8.Value = Me.lstMyData.Column(7, i)
    
    Me.TextBox9.Value = Me.lstMyData.Column(8, i)
    Me.ComboBox9.Value = Me.lstMyData.Column(8, i)
    
    Me.TextBox10.Value = Me.lstMyData.Column(9, i)
    Me.ComboBox10.Value = Me.lstMyData.Column(9, i)

End Sub

Private Sub ToggleButton1_Click()

    Dim i As Integer

    i = Me.lstMyData.ListIndex
    Me.lstMyData.Selected(i) = True
    
    Me.TextBox1.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox1.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox2.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox2.Value = Me.lstMyData.Column(0, i - 1)

    Me.TextBox3.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox3.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox4.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox4.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox5.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox5.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox6.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox6.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox7.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox7.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox8.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox8.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox9.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox9.Value = Me.lstMyData.Column(0, i - 1)
    
    Me.TextBox10.Value = Me.lstMyData.Column(0, i - 1)
    Me.ComboBox10.Value = Me.lstMyData.Column(0, i - 1)

    Me.lstMyData.Selected(i - 1) = True

End Sub

Private Sub ToggleButton2_Click()

    Dim i As Integer

    i = Me.lstMyData.ListIndex
    Me.lstMyData.Selected(i) = True
    
    Me.TextBox1.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox1.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox2.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox2.Value = Me.lstMyData.Column(0, i + 1)

    Me.TextBox3.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox3.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox4.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox4.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox5.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox5.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox6.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox6.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox7.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox7.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox8.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox8.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox9.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox9.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.TextBox10.Value = Me.lstMyData.Column(0, i + 1)
    Me.ComboBox10.Value = Me.lstMyData.Column(0, i + 1)
    
    Me.lstMyData.Selected(i + 1) = True

End Sub

Private Sub UserForm_Initialize()

    Dim x As Integer

    For x = 0 To Me.lstMyData.ListCount - 1
        If Me.lstMyData.Selected(c) Then Me.lstMyData.Selected(x) = False
    Next x
    
End Sub

Private Sub cmdUpdate_Click()

Dim rowslect As Double
Dim i As Integer
Dim wb As Workbook
Dim ws As Worksheet

Set ws = Worksheets("Sheet3")
i = Me.lstMyData.ListIndex
rowselect = i + 1

With ws
    .Cells((i + 2), 1).Value = Me.TextBox1.Value
    .Cells((i + 2), 2).Value = Me.TextBox2.Value
    .Cells((i + 2), 3).Value = Me.TextBox3.Value
    .Cells((i + 2), 4).Value = Me.TextBox4.Value
    .Cells((i + 2), 5).Value = Me.TextBox5.Value
    .Cells((i + 2), 6).Value = Me.TextBox6.Value
    .Cells((i + 2), 7).Value = Me.TextBox7.Value
    .Cells((i + 2), 8).Value = Me.TextBox8.Value
    .Cells((i + 2), 9).Value = Me.TextBox9.Value
    .Cells((i + 2), 10).Value = Me.TextBox9.Value
End With

End Sub

Set a breakpoint or a `Debug.Print` statement inside of your `cmdUpdate_Click` sub and double-check the values in your textboxes. Sometimes that values visible on the form are not the values accessed in the code. Look at [this answer](https://stackoverflow.com/a/2844288/4717755) for an interesting discussion. — PeterT, Apr 13 '21 at 14:51
Hey @PeterT - Thanks for the info! I've added the Debug.Print statement to the sub, and I can see that the other textbox values are not being pulled thorough. I can change the data in the first column of the table, but everything else doesn't seem to be working. I changed the .value to .text, but it didn't make a difference — Karanvir.S.G, Apr 13 '21 at 15:16
Why are you populating both a textbox and a combobox for each item? And you code looks quite odd in places: eg. `lstMyData_Click` and `ToggleButton1_Click` use different code presumably to access the same data? How is `lstMyData` initially populated? — Tim Williams, Apr 13 '21 at 16:24

score 0 · Answer 1 · answered Apr 13 '21 at 20:48

It looks like the first update to the sheet triggers the lstMyData_Click() event which refreshes the other text boxes with their original values. To prevent this you could use a global variable as a flag to indicate when updates are being made.

Option Explicit
Dim bUpdate As Boolean

Private Sub cmdUpdate_Click()

    Dim wb As Workbook, ws As Worksheet
    Dim iRow As Long, n As Integer
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet3")
    
    iRow = Me.lstMyData.ListIndex + 1
    If iRow > 0 Then
        bUpdate = True
        For n = 1 To 10
            ws.Cells(iRow, n) = Me.Controls("TextBox" & n).Value
        Next
        bUpdate = False
    End If
    
End Sub

Private Sub lstMyData_Click()

    If bUpdate Then Exit Sub

    Dim i As Long, n As Integer
    i = Me.lstMyData.ListIndex
    For n = 1 To 10
        Me.Controls("TextBox" & n).Value = Me.lstMyData.Column(n - 1, i)
        Me.Controls("ComboBox" & n).Value = Me.lstMyData.Column(n - 1, i)
    Next

End Sub

Private Sub cmdRefresh_Click()

    Dim X As Long, n As Integer
    For X = 0 To Me.lstMyData.ListCount - 1
        If Me.lstMyData.Selected(X) Then Me.lstMyData.Selected(X) = False
    Next X
    For n = 1 To 10
        Me.Controls("TextBox" & n).Value = ""
        Me.Controls("ComboBox" & n).Value = ""
    Next

End Sub

Private Sub ToggleButton1_Click()

    Dim i As Long, n As Integer
    i = Me.lstMyData.ListIndex
    If i < 0 Then
       i = 0
    ElseIf i > 0 Then
       i = i - 1
    End If
    Me.lstMyData.Selected(i) = True
    Call lstMyData_Click

End Sub

Private Sub ToggleButton2_Click()

    Dim i As Long, n As Integer
    i = Me.lstMyData.ListIndex
    If i < Me.lstMyData.ListCount - 1 Then
       i = i + 1
    End If
    Me.lstMyData.Selected(i) = True
    Call lstMyData_Click

End Sub

Private Sub UserForm_Initialize()
    Call cmdRefresh_Click
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

How to Update Entry In User Form?

1 Answers1