Using a UserForm in VBA write a code.The UserForm has 4 option button.Each one h

Question

Using a UserForm in VBA write a code.The UserForm has 4 option button.Each one has a specific function: I. Using column D find all items that have a 7% discount 2.Using column A find all products wich code start with letter E 3.Using column B find all products whose price is from $50 to $100 4.Using column C, find values from 5 to 20 Every step needs to be done every time you select their option button and press calculate.For example If you select option 1 you need to press the calculate button for the results to show in the UserForm. The reset button needs to reset the list.THE CODE NEEDS LOOP INSTRUCTIONS

Explanation / Answer

'add 4 option buttons, 2 command buttons and 1 listbox to your userform

Private Sub CommandButton1_Click()
Dim lastrow
lastrow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).row
Dim row
row = 0
ListBox1.ColumnCount = 4
ListBox1.AddItem
ListBox1.List(0, 0) = "Product Name" 'listbox is 0 indexed
ListBox1.List(0, 1) = "Price"
ListBox1.List(0, 2) = "Values"
ListBox1.List(0, 3) = "Discount(%)"

If (OptionButton1.Value = True) Then 'that have 7% discount in column D
'loop from row2 to end as row1 is header
For i = 2 To lastrow
If (Sheet1.Cells(i, 4) = 7) Then
row = row + 1
ListBox1.AddItem
ListBox1.List(row, 0) = Sheet1.Cells(i, 1) 'listbox is 0 indexed
ListBox1.List(row, 1) = Sheet1.Cells(i, 2)
ListBox1.List(row, 2) = Sheet1.Cells(i, 3)
ListBox1.List(row, 3) = Sheet1.Cells(i, 4)
End If
Next i
ElseIf (OptionButton2.Value = True) Then 'product code starts with E from column A
'loop from row2 to end as row1 is header
For i = 2 To lastrow
If (UCase(Left(Sheet1.Cells(i, 1), 1)) = "E") Then
row = row + 1
ListBox1.AddItem
ListBox1.List(row, 0) = Sheet1.Cells(i, 1) 'listbox is 0 indexed
ListBox1.List(row, 1) = Sheet1.Cells(i, 2)
ListBox1.List(row, 2) = Sheet1.Cells(i, 3)
ListBox1.List(row, 3) = Sheet1.Cells(i, 4)
End If
Next i
ElseIf (OptionButton3.Value = True) Then 'price from 50 to 100 from column B
For i = 2 To lastrow
If (Sheet1.Cells(i, 2) >= 50 And Sheet1.Cells(i, 2) <= 100) Then
row = row + 1
ListBox1.AddItem
ListBox1.List(row, 0) = Sheet1.Cells(i, 1) 'listbox is 0 indexed
ListBox1.List(row, 1) = Sheet1.Cells(i, 2)
ListBox1.List(row, 2) = Sheet1.Cells(i, 3)
ListBox1.List(row, 3) = Sheet1.Cells(i, 4)
End If
Next i
ElseIf (OptionButton4.Value = True) Then 'Values from 5 to 20 from column c
For i = 2 To lastrow
If (Sheet1.Cells(i, 3) >= 5 And Sheet1.Cells(i, 3) <= 20) Then
row = row + 1
ListBox1.AddItem
ListBox1.List(row, 0) = Sheet1.Cells(i, 1) 'listbox is 0 indexed
ListBox1.List(row, 1) = Sheet1.Cells(i, 2)
ListBox1.List(row, 2) = Sheet1.Cells(i, 3)
ListBox1.List(row, 3) = Sheet1.Cells(i, 4)
End If
Next i
Else
MsgBox "Please Select an option"
End If
End Sub

Private Sub CommandButton2_Click()
'clear listbox
ListBox1.Clear
End Sub

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.