PDA

View Full Version : Userform with listbox and checkboxes that finds values on a worksheet



jslings
11-21-2019, 11:34 AM
Hello! I am trying to create a user form that makes a user select a name from a Listbox and two options from checkboxes and returns the average of "days" and "amount" for that name selected from the listbox . I'm new to VBA and having trouble setting up the code to do so. Does anyone have any pointers? My userform is attached below, any help is appreciated, and this is what i have so far for code.



Private Sub BtnCancel_Click()
MsgBox ("Report Cancelled")
Unload FrmCust
End Sub



Private Sub BtnOk_Click()

Dim customer As Range

'For Each customer In allcustomers



If ListBoxCustlist.ListIndex = -1 Then
MsgBox "No Item was selected"
Else
MsgBox ' customers name and how much they owe / how many days if selected
End If




End Sub

Private Sub UserForm_Initialize()

' Fill the listbox with each user's name

Dim cell As Range

'Load to ListBox

FrmCust.ListBoxCustlist.MultiSelect = fmMultiSelectSingle 'allows only one selection

For Each cell In Worksheets("Data").Range("A3:A283")
ListBoxCustlist.AddItem cell.Value
Next cell



End Sub

paulked
11-21-2019, 12:03 PM
Hi and welcome to the forum.

This should get you started:


Private Sub BtnOK_Click()
Dim rw&, lr&, dys&, amt&, msg1$, msg2$
If ListBoxCustlist.ListIndex = -1 Then MsgBox "Please select a customer.": Exit Sub
If Not CheckBox1 And Not CheckBox2 Then MsgBox "Please select Days and/or Amount.": Exit Sub
lr = Cells(Rows.Count, 1).End(3).Row
For rw = 3 To lr
If Cells(rw, 1) = ListBoxCustlist.Value Then dys = dys + Cells(rw, 2): amt = amt + Cells(rw, 3)
Next
If CheckBox1 Then msg1 = dys & " Days."
If CheckBox2 Then msg2 = Format(amt, "£#,###.00")
MsgBox msg1 & vbCr & msg2
End Sub