Create a user form and extract certain data from the excel file via VBA

Question

1. About data

Initially, in the user form, I have 2 check boxes. The first one is User_Type (that contains 3 check boxes 1, 2, 3) and the second one is User_Size (that contains 4 check boxes S,M,L,XL). The master data sheet contains 5 columns: Type, Size, Quantity, Price_per_unit, Total_price. The "Type" column includes 1,2,3 and "Size" column includes "S", "M", "L", "XL".

2. Expected output

How can I add to the below VBA code that works this way: If User_Type = Type and User_Size = Size, the relevant content of all 5 columns will be copied and pasted to another sheet?

3. Example

Type	Size	Quantity	Price_per_unit	Total_price
1	S	3	10	30
2	M	15	3	45
2	S	1	40	40
3	L	4	20	80
1	XL	7	5	35

For example, if I tick on the checkboxes 1 and 2 of User_Type and "S" of User_Size, the following information is supposed to be in another sheet:

Type	Size	Quantity	Price_per_unit	Total_price
1	S	3	10	30
2	S	1	40	40

Furthermore, I also get stuck in creating a user form containing the checkboxes

Private Sub CommandButton1_Click()
    UserInputForm.Show
End Sub

Private Sub UserForm ()
    'Fill TypeCheckBox
    With TypeListBox
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
    
    'Fill SizeCheckBox
    With SizeCheckBox
        .AddItem "S"
        .AddItem "M"
        .AddItem "L"
        .AddItem "XL"
    
    End With
End Sub

Answer 1

You will need to add a command button in the UserForm and add this code on the click activity:

Code inside UserInputForm:

Private Sub CommandButton1_Click()
    Call filtering
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    'Fill TypeCheckBox
    With TypeListBox
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
    End With
    
    'Fill SizeCheckBox
    With SizeCheckBox
        .AddItem "S"
        .AddItem "M"
        .AddItem "L"
        .AddItem "XL"
    End With
    
End Sub

Then, this module will help you with the filtering and pasting in a different worksheet.

Code for Module:

Sub filtering()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim typeColumnNumber As Long, sizeColumnNumber As Long
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1") 'Update as needed
    Set ws2 = ThisWorkbook.Sheets("Sheet2") 'Update as needed
    typeColumnNumber = 1 'Update as needed
    sizeColumnNumber = 2 'Update as needed
    
    Dim sizeFilter As String, typeFilter As String
    typeFilter = UserInputForm.TypeListBox.Value
    sizeFilter = UserInputForm.SizeCheckBox.Value
    
    Dim dataRange As Range, destinationRange As Range
    Set destinationRange = ws2.Range("A1") 'Update as needed
    Set dataRange = ws1.UsedRange
    
    'Clear filters
    If dataRange.AutoFilter Then dataRange.AutoFilter
    
    'Clear previous data
    ws2.UsedRange.ClearContents
    
    With dataRange
        .AutoFilter Field:=sizeColumnNumber, Criteria1:="=" & sizeFilter
        .AutoFilter Field:=typeColumnNumber, Criteria1:="=" & typeFilter
        If .Columns(sizeColumnNumber).SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Copy
        Else
            GoTo exitpoint
        End If
    End With
    
    destinationRange.PasteSpecial xlPasteValues
    
    'Clear filters
    dataRange.AutoFilter
    
    Exit Sub
    
exitpoint:
    dataRange.AutoFilter
    MsgBox "No lines for selected criteria."
End Sub

Sub FormShow()
    UserInputForm.Show
End Sub