Can I use VBA to update a dataset?

Question

I'm using a vba UserForm to enter in "Vehicle Reg" and choose "Current Status" from a combobox, which currently adds this data to a new line along with a uniqueID, the username, and the time.

I'd like to create a finite list of Vehicle Reg, and use the UserForm to update the Current Status (1 column to the right) with the new value.

Is there a way I can alter what I have to make this work? I am brand new to vba and have been googling for days! I have a Submit Button, a Reset Button, and on the main spreadsheet I have a macro button that I have attached the "Show Form".

Here's what I have:

Sub Reset()

    Dim iRow As Long
    
    iRow = [Counta(Database!A:A)] 'identifying the last row
    
    With frmForm
         .txtID.Value = ""
        
        .cmbStatus.Clear
        
        .cmbStatus.AddItem "Loaded - In"
        .cmbStatus.AddItem "Loaded - Out"
        .cmbStatus.AddItem "Empty - Parked"
        .cmbStatus.AddItem "Empty - On Bay"
        
        .lstDatabase.ColumnCount = 4
        .lstDatabase.ColumnHeads = True
        
        
        If iRow > 1 Then
        
            .lstDatabase.RowSource = "Database!A2:C" & iRow
            
        Else
        
            .lstDatabase.RowSource = "Database!A2:C2"
            
        End If
        
       
    End With
End Sub

Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
    
    Set sh = ThisWorkbook.Sheets("Database")
    
    iRow = [Counta(Database!A:A)] + 1
    
    With sh
    
    .Cells(iRow, 1) = iRow - 1
    .Cells(iRow, 2) = frmForm.txtID.Value
    .Cells(iRow, 3) = frmForm.cmbStatus.Value
    .Cells(iRow, 4) = Application.UserName
    .Cells(iRow, 5) = [Text(Now(), "DD-MM-YYY HH:MM:SS")]
    
    
    End With
    
End Sub

Sub Show_Form()
    
    Call Reset
    
    frmForm.Show
           
End Sub

**And my initialize code:**

    Private Sub UserForm_Initialize()

    With frmForm
       
        Height = 370
        Width = 645
        
    End With
    
End Sub

Private Sub cmdReset_Click()

  Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    
    Call Reset
    
End Sub


Private Sub cmdSave_Click()

With Me

    If txtID.Value = "" Then
    MsgBox "Reg Cannot Be Blank", vbOKOnly + vbCritical + vbDefaultButton1, "Reg Blank"
           
    End If
    
    Exit Sub
    
End With

    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to save?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    
        
    Call Submit
    Call Reset
    
    Unload Me   
    
End Sub


Private Sub frmForm_Initialize()

    Call Reset
    
End Sub

Thanks!

Answer 1

Use the lstDatabase_Click() event together with the .ListIndex property to load the form items from the sheet. The Reset de-selects the line. Save without a line selected adds a new record, with line selected updates the existing record.

Option Explicit

Sub Show_Form()
    frmForm.Show
End Sub

Private Sub cmdReset_Click()
    ' unselect line lstdatabase
    With Me
        .txtID.Value = ""
        .cmbStatus = ""
        .lstDatabase.ListIndex = -1
    End With
End Sub

Private Sub UserForm_Initialize()
    With Me
        .Height = 370
        .Width = 645
        .txtID.Value = ""
        .cmbStatus.Clear
        .cmbStatus.AddItem "Loaded - In"
        .cmbStatus.AddItem "Loaded - Out"
        .cmbStatus.AddItem "Empty - Parked"
        .cmbStatus.AddItem "Empty - On Bay"
    
        .lstDatabase.ColumnCount = 4
        .lstDatabase.ColumnHeads = True
    End With
    LoadDatabase
End Sub

Private Sub cmdSave_Click()

    If txtID.Value = "" Then
        MsgBox "Reg Cannot Be Blank", vbCritical, "Reg Blank"
    ElseIf cmbStatus.Value = "" Then
        MsgBox "Status Cannot Be Blank", vbCritical, "Status Blank"
    ElseIf MsgBox("Do you want to save?", vbYesNo, "Confirmation") = vbYes Then
        Call Submit
        'Unload Me
    End If
    
End Sub

Sub Submit()

    Dim i As Long, r As Long
    With lstDatabase
        i = .ListIndex
        ' is line selected
        If i >= 0 Then r = .List(i, 0) + 1
    End With
        
    ' update or add
    With Sheets("Database")
        ' add new find last line
        If r = 0 Then
            r = 1 + .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(r, 1) = r - 1
            .Cells(r, 2) = frmForm.txtID.Value
        End If
        ' update
        .Cells(r, 3) = frmForm.cmbStatus.Value
        .Cells(r, 4) = Application.UserName
        .Cells(r, 5) = [Text(Now(), "DD-MM-YYY HH:MM:SS")]
    End With
          
End Sub

Private Sub lstDatabase_Click()

    Dim i As Long, r As Long
    With lstDatabase
        i = .ListIndex
        If i >= 0 Then
           txtID.Value = .List(i, 1)
           cmbStatus.Text = .List(i, 2)
        End If
    End With

End Sub

Sub LoadDatabase()

   Dim iRow As Long
   With Sheets("Database")
       iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With
   If iRow < 2 Then iRow = 2
   lstDatabase.RowSource = "Database!A2:C" & iRow

End Sub