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

J15491

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,144,370
Messages
5,723,964
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top