How to update spreadsheet from VBA Form?

Question

Doing a project for school and I can get the data to read into my form but I can't seem to update from my form and then move onto a new record. Can someone please help.

Private Sub Next_Command_Click()
    Do
        nCurrentRow = nCurrentRow + 1
        TraverseData (nCurrentRow)
    Loop Until NC_C_L.Cells(nCurrentRow, 1).Value = "" Or NC_C_L.Cells(nCurrentRow, 1).Value = Me.***_Text.Value
End Sub

Private Sub Previous_Command_Click()
    Do
        nCurrentRow = nCurrentRow - 1
        TraverseData (nCurrentRow)
    Loop Until nCurrentRow = 1 Or NC_C_L.Cells(nCurrentRow, 1).Value = Me.***_Text.Value
End Sub

Private Sub TraverseData(nRow As Long)
        Me.***_Text.Value = NC_C_L.Cells(nRow, 1)
        Me.***_Box = NC_C_L.Cells(nRow, 2)
        Me.***_Combo.Value = NC_C_L.Cells(nRow, 3)
        Me.***_Combo.Value = NC_C_L.Cells(nRow, 4)
        Me.***_Combo.Value = NC_C_L.Cells(nRow, 5)
        Me.***_Combo.Value = NC_C_L.Cells(nRow, 6)
        Me.***_Combo.Value = NC_C_L.Cells(nRow, 7)
        Me.***_Text.Value = NC_C_L.Cells(nRow, 8)
        Me.***_Text.Value = NC_C_L.Cells(nRow, 9)
        Me.Comments1_Text.Value = NC_C_L.Cells(nRow, 10)
        Me.Comments2_Text.Value = NC_C_L.Cells(nRow, 11)
        Me.Comments3_Text.Value = NC_C_L.Cells(nRow, 12)
        Me.PhoneNumber_Text.Value = NC_C_L.Cells(nRow, 13)
        Me.Address1_Text.Value = NC_C_L.Cells(nRow, 14)
        Me.Address2_Text.Value = NC_C_L.Cells(nRow, 15)
        Me.City_Text.Value = NC_C_L.Cells(nRow, 16)
        Me.State_Combo.Value = NC_C_L.Cells(nRow, 17)
        Me.Zip_Text.Value = NC_C_L.Cells(nRow, 18)
        Me.EMail_Text.Value = NC_C_L.Cells(nRow, 19)
        Me.P_Name_Text.Value = NC_C_L.Cells(nRow, 20)
        Me.P_PhoneNumber_Text.Value = NC_C_L.Cells(nRow, 21)
        Me.P_Address_Text.Value = NC_C_L.Cells(nRow, 22)
    End Sub

(I did modify some of the names to reflect ***.)

Answer 1

Usually you'll see two separate methods.

Method1: Transfer the range data into the form controls:

Private Sub TraverseDataToForm(nRow As Long)
        Me.***_Text.Value = NC_C_L.Cells(nRow, 1).Value
        Me.***_Box.Value = NC_C_L.Cells(nRow, 2).Value
End sub

Method2: Transfer the from controls data into the range:

Private Sub TraverseDataToRange(nRow As Long)
        NC_C_L.Cells(nRow, 1).Value = Me.***_Text.Value 
        NC_C_L.Cells(nRow, 2).Value = Me.***_Box.Value
End sub

SyncValues will link the Range to MSForms.Control.ControlSource and update the values when the record is changed.

Note: You'll also have to trigger an update the values when the userform closes.

---

Sub SyncValues(ctrl As MSForms.Control, Target As Range)

    If ctrl.ControlSource <> "" Then
        Range(ctrl.ControlSource).Value = ctrl.Value
    End If

    If Not Target Is Nothing Then
        ctrl.ControlSource = Target.Address(True, True, xlA1, True)
    End If
End Sub

Private Sub UserForm_Deactivate()
    TraverseData nCurrentRow
End Sub

Private Sub Next_Command_Click()
    Do
        nCurrentRow = nCurrentRow + 1
        TraverseData nCurrentRow
    Loop Until NC_C_L.Cells(nCurrentRow, 1).Value = "" Or NC_C_L.Cells(nCurrentRow, 1).Value = Me.AAA_Text.Value
End Sub

Private Sub Previous_Command_Click()
    Do
        nCurrentRow = nCurrentRow - 1
        TraverseData nCurrentRow
    Loop Until nCurrentRow = 1 Or NC_C_L.Cells(nCurrentRow, 1).Value = Me.AAA_Text.Value
End Sub

Private Sub TraverseData(nRow As Long)
    If nRow = 0 Then Exit Sub
    SyncValues Me.AAA_Text, NC_C_L.Cells(nRow, 1)
    SyncValues Me.BBB_Box, NC_C_L.Cells(nRow, 2)
    SyncValues Me.CCC_Combo, NC_C_L.Cells(nRow, 3)
    SyncValues Me.DDD_Combo, NC_C_L.Cells(nRow, 4)
    SyncValues Me.EEE_Combo, NC_C_L.Cells(nRow, 5)
    SyncValues Me.FFF_Combo, NC_C_L.Cells(nRow, 6)
    SyncValues Me.GGG_Combo, NC_C_L.Cells(nRow, 7)
    SyncValues Me.HHH_Text, NC_C_L.Cells(nRow, 8)
    SyncValues Me.III_Text, NC_C_L.Cells(nRow, 9)
    SyncValues Me.Comments1_Text, NC_C_L.Cells(nRow, 10)
    SyncValues Me.Comments2_Text, NC_C_L.Cells(nRow, 11)
    SyncValues Me.Comments3_Text, NC_C_L.Cells(nRow, 12)
    SyncValues Me.PhoneNumber_Text, NC_C_L.Cells(nRow, 13)
    SyncValues Me.Address1_Text, NC_C_L.Cells(nRow, 14)
    SyncValues Me.Address2_Text, NC_C_L.Cells(nRow, 15)
    SyncValues Me.City_Text, NC_C_L.Cells(nRow, 16)
    SyncValues Me.State_Combo, NC_C_L.Cells(nRow, 17)
    SyncValues Me.Zip_Text, NC_C_L.Cells(nRow, 18)
    SyncValues Me.EMail_Text, NC_C_L.Cells(nRow, 19)
    SyncValues Me.P_Name_Text, NC_C_L.Cells(nRow, 20)
    SyncValues Me.P_PhoneNumber_Text, NC_C_L.Cells(nRow, 21)
    SyncValues Me.P_Address_Text, NC_C_L.Cells(nRow, 22)
End Sub