vba to reset a userform list box (lstDatabase) with multiple lines of data .

[J15491]

I have a user form which contains multiple text and combo boxes. When the user clicks the save command button the data is populated to the list box (lstDatabase). Currently I can edit or delete individual rows with a button for each once a row is selected. I want to be able to click a command button (cmdNewMonth), save the workbook by the month, year and then clear all rows of data (lstDatabase) to start a new month. I can't seem to find exactly what I want to do. Below is the code for my userform so far.

Option Explicit

Private Sub cmbVendor_Click()
        With Me

        .txtVendorAddress = Application.WorksheetFunction.VLookup(Me.cmbVendor, Sheet5.Range("A2:D30"), 2, False)
        .txtVendorLocation = Application.WorksheetFunction.VLookup(Me.cmbVendor, Sheet5.Range("A2:D30"), 3, False)
       
    End With

End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub cmdDelete_Click()

    If Selected_List = 0 Then
   
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
    End If
   
    Dim i As VbMsgBoxResult
   
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "Confirmation")
   
    If i = vbNo Then Exit Sub
   
    ThisWorkbook.Sheets("Database").Rows(Selected_List + 1).Delete
   
    Call Reset
   
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"
   


End Sub

Private Sub cmdEdit_Click()
   
    If Selected_List = 0 Then
   
        MsgBox "No row is Selected.", vbOKOnly + vbInformation, "Edit"
       
        Exit Sub
       
    End If

    'Code to update the value to respective controls
   
   
    Me.txtRowNumber.Value = Selected_List + 1
   
    Me.txtName.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0)
   
    Me.txtAddress.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1)

    Me.txtFamily.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
   
    Me.txtFamilyMembers.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
   
    Me.cmbLocation.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
   
    Me.txtPhone.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
   
    Me.txtAccountNumber.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
   
    Me.cmbVendor.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
   
    Me.txtVendorAddress.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
   
    Me.txtVendorLocation.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 9)
   
    Me.txtConfirmation.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 10)

    Me.txtClientOwes.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 11)

    Me.txtClientPaid.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 12)
   
    Me.txtTSAPledge.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 13)
   
    Me.txtAgency.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 14)
   
    Me.txtPledge.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 15)
   
    Me.txtOtherAgency.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 16)
   
    Me.txtOtherPledge.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 17)
   
    Me.cmbCategory.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 18)
   
    Me.cmbSource.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 19)
   
    MsgBox "Please make the required changes and click the 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
   
   
End Sub

Private Sub cmdNewMonth_Click()

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()

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


Private Sub UserForm_Initialize()

    Call Reset
   
'Populate Vendor combo box

Dim rngVendor As Range
Dim ws As Worksheet
Set ws = Worksheets("VLookupList")
For Each rngVendor In ws.Range("Vendor")

Me.cmbVendor.AddItem rngVendor.Value

Next rngVendor
   
End Sub

Function Selected_List() As Long

    Dim i As Long
   
    Selected_List = 0
   
    For i = 0 To frmForm.lstDatabase.ListCount - 1
   
        If frmForm.lstDatabase.Selected(i) = True Then
       
            Selected_List = i + 1
            Exit For
        End If
       
    Next i
   
   
End Function

 

[Gokhan Aycan]

When you say "clear all rows of data" will you be deleting the data on the worksheet? lstDatabase.Clear would clear it. Not sure if this is what you are asking though. Saving a workbook is also a trivial task.

Is your listbox multi-select? Selected_List function looks unnecessary otherwise.

You can populate vendor combobox in one line: Me.cmbVendor.List = ws.Range("Vendor")

 

[J15491]

I did not have the lstDatabase property set multi-select. I will make the changes you suggested in the morning. Thank you for your help.