VBA UserForm: Filter data in between dates using sheet name (FYI: i have multiple sheets, around 20-30 of them)

[wcanarejo]

Hi everyone,

I am new to Excel VBA, and I have some issues filtering data in between dates.
I Just want to be able to filter entries (from date-to date) using the sheetname as a criteria.

This is how the form look like( please see attached photo)


Or if you have any way get this sorted out, please do suggest. I really need your help.
Thank you very much

 

[wcanarejo]

Ive been looking for this online but I cant find one.
By the way this is a DTR, and I just want to filter by date and sum up time in timeout of the employee ang get their total hours for the entire week

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[wcanarejo]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hi mumps,
This is an xlb22 of my sheet.

DataEntry.xlsb
	A	B	C	D	E	F	G	H	I	J
1	No.	ID No.	Employee	Date	Time In	Time Out	Break	Total Hours	Site Location	Logged Activities
2	1	1002	Nino Yec	06/03/21	7:00AM	17:00PM	0.3	9.7	14 Bauchop	6/3/2021 20:24
3	2	1004	Emmanuel Marzan	06/02/21	7:00AM	15:00PM	0.5	7.5	BellRoad	6/4/2021 5:27
4	3	1005	Marvel Domingues	06/02/21	6:00AM	14:00PM	0.5	7.5	14 Bauchop	6/4/2021 5:29
5	4	1007	Romeo Bandivas	06/05/21	8:00AM	16:00PM	0	8	27 Waiwhetu	6/4/2021 5:30
6	5	1003	Ruffin Cortes	06/02/21	5:00AM	13:00PM	0.3	7.7	Red Cross	6/4/2021 5:31
7	6	1003	Ruffin Cortes	06/05/21	6:00AM	15:00PM	0.5	8.5	148 Riverside	6/4/2021 7:07
8	7	1003	Nino Yec	06/05/21	6:00AM	14:00PM	0.5	7.5	318 Oxford Terrace	6/4/2021 18:32
9	8	1004	Efren Galang	06/04/21	6:00AM	14:00PM	0.5	7.5	Red Cross	6/4/2021 18:35
10	9	1004	Efren Galang	06/07/20	7:00AM	15:00PM	0.5	7.5	176 Cambridge	6/4/2021 21:17
11	10	1006	oleg	05/05/21	7:00AM	15:00PM	0	8	176 Cambridge	6/5/2021 4:43
12	11	1003	Nino Yec	06/01/21	4:00AM	16:00PM	0	12	176 Cambridge	6/5/2021 6:54
13	12	1003	Nino Yec	06/02/21	5:00AM	16:00PM	0.5	10.5	14 Bauchop	6/5/2021 6:55
14	13	1002	Ruffin Cortes	06/07/21	2:00AM	14:00PM	0.5	11.5	14 Bauchop	6/5/2021 18:10
15	14	1002	Ruffin Cortes	06/01/21	6:00AM	15:00PM	0.5	8.5	14 Bauchop	6/5/2021 18:13
16	15	1002	Ruffin Cortes	06/03/21	3:00AM	13:00PM	0.5	9.5	Red Cross	6/5/2021 18:14
17	16	1002	Ruffin Cortes	06/04/20	7:00AM	12:00PM	0	5	148 Riverside	6/5/2021 18:14
18	17	1002	Ruffin Cortes	06/04/21	13:00PM	16:00PM	0	3	Red Cross	6/5/2021 18:16
Data

Cell Formulas
Range	Formula
A4:A18	A4	=ROW()-1

 

[wcanarejo]

I assigned 1 sheet for the employee dedicated for their time entries. So every time I am adding an entry, it will be double saved from this sheet to the sheet named after them. I think it's a bit redundant, but if you could help me simplify this.

 

[mumps]

Thank you for the sheet data. However, in your original post, you had a drop down list of sheet names, date from, date to and a command button. I don't see these in Post #4. Are you using a userform which contains this data? If so, I would need access to the userform. It might be easier if you uploaded a copy of your file to box.com or dropbox.com and posted the link here.

 

[wcanarejo]

Thank you for the sheet data. However, in your original post, you had a drop down list of sheet names, date from, date to and a command button. I don't see these in Post #4. Are you using a userform which contains this data? If so, I would need access to the userform. It might be easier if you uploaded a copy of your file to box.com or dropbox.com and posted the link here.

Yes I am using a userform. Those data assigned to the dropdownlist are the name of the sheets I have in this workbook. I am doing a double saving method to be able to save selected on the sheets named after them. Let me show you a sample.

DataEntry.xlsb
	A	B	C	D
1	Date	ID No	Name	Total Hrs
2	6/2/2021	1002	Ruffin Cortes	8
3	6/7/2021	1002	Ruffin Cortes	11.5
4	6/1/2021	1002	Ruffin Cortes	8.5
5	6/3/2021	1002	Ruffin Cortes	9.5
6	6/4/2120	1002	Ruffin Cortes	5
7	6/4/2021	1002	Ruffin Cortes	3
Ruffin Cortes

this is a sheet named after this employee. What I want is that, whenever i go to the userform I made, I will chose their name on the dropdown, then the listbox will get the data from sheets that are name after the employee then from their I would be able to filter the data using date range filtering.

the dropdown process for each employee works fine, it perfectly pitching the data from their assigned sheets. the filtering by date is where im stuck at.

 

[mumps]

I would need the names of the userform objects and also to see the code you are currently using. The easiest way to do this is to upload a copy of your file as I suggested in Post #3.

 

[wcanarejo]

Yeah, No big deal. Heres the Link. DataEntry.xlsb
Im just new to this so, if you can help me simplify it, that would be greatly appreciated. Thank you so much for your help.

 

[mumps]

Replace your current code with this:

Private Sub ComboBox1_Change()
    Me.ListBox1.Clear
    Me.Label6 = Sheets(ComboBox1.Value).Name
    TextBox1.SetFocus
End Sub

Private Sub TextBox1_AfterUpdate()
    Me.TextBox1 = CDate(Me.TextBox1)
    TextBox2.SetFocus
End Sub

Private Sub TextBox2_AfterUpdate()
    Application.ScreenUpdating = False
    Dim beginDate As Long, endDate As Long, srcWS As Worksheet, rng As Range, LastRow As Long, total As Double, C As Long
    Set srcWS = Sheets(ComboBox1.Value)
    Me.TextBox1 = CDate(Me.TextBox1)
    beginDate = DateValue(Me.TextBox1.Value)
    endDate = DateValue(Me.TextBox2.Value)
    ldatefrom = DateSerial(Day(beginDate), Month(beginDate), Year(beginDate))
    ldateto = DateSerial(Day(endDate), Month(endDate), Year(endDate))
    Me.ListBox1.Clear
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(beginDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)
        For Each rng In .Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible)
            If rng.Row > 1 Then
                total = total + rng.Offset(, 3).Value
            End If
            Me.ListBox1.AddItem
            For C = 0 To 3
                Me.ListBox1.List(ListBox1.ListCount - 1, C) = .Cells(rng.Row, C + 1)
            Next C
        Next rng
        .Range("A1").AutoFilter
        Label5.Caption = "Total Hours: " & total
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    For i = 2 To Sheets.Count
        Me.ComboBox1.AddItem Sheets(i).Name
    Next i
End Sub