Stainless02
New Member
- Joined
- Sep 21, 2015
- Messages
- 21
Good morning Excel Gurus
I would love some assistance in getting my dream staff task tracker up and running.
I manage a team of 15 pers and they have a variety of tasks that they complete each day. It has been requested that I provide some visibility of how long is spent on each task. To do this as I do not want to add unnecessary admin work to their day I created an excel sheet with a UserForm on it so that when they open the workbook it pops up, they select the date (on a calendar), select their name from ComboBox1 and the task they complete from ComboBox2 (both have lists associated with them). They then type in TextBox2 how many hours they worked. Click CommanbButton1 (submit) and the next blank row is found and the data is input into the following columns: Date-A, Name-B, Task-C, Hours worked-D and notes in E.
This is all working well so far.
Now onto the actual problem – I have created UserForm2 and this form is for my use only. The form include two calendars, two ListBoxes (filled from the same lists as above) and a CommandButton (Submit). The way I would like it to work is: I pick the start date, the end date, the staff and tasks I want to track and click submit. The filter sorts the data on Sheet1 with only the details between those dates, the staff selected and the task selected, it then SUMS the hours (D) and gives me that answer.
e.g. 01 Mar 17 – 20 Mar 17, Staff No 3, worked on Task 6 for 3 hours. All I need is the “3” in this case.
Is there a way of doing this? I am using Excel 2003 and have got the calendars working to output the date into Cell F1, F2 on the Filters sheet. I thought I would then be able to use these dates to further drill down but am in a bit over my head at the moment.
The code looks like this from the CommandButton (Submit)
Private Sub CommandButton2_Click()
Dim i As Integer
i = 1
While ThisWorkbook.Worksheets("Filters").Range("A" & i).Value <> ""
i = i + 1
Wend
ThisWorkbook.Worksheets("Filters").Range("F1").Value = Calendar1.Value
ThisWorkbook.Worksheets("Filters").Range("F2").Value = Calendar2.Value
End Sub
Any help is appreciated.
Cheers
I would love some assistance in getting my dream staff task tracker up and running.
I manage a team of 15 pers and they have a variety of tasks that they complete each day. It has been requested that I provide some visibility of how long is spent on each task. To do this as I do not want to add unnecessary admin work to their day I created an excel sheet with a UserForm on it so that when they open the workbook it pops up, they select the date (on a calendar), select their name from ComboBox1 and the task they complete from ComboBox2 (both have lists associated with them). They then type in TextBox2 how many hours they worked. Click CommanbButton1 (submit) and the next blank row is found and the data is input into the following columns: Date-A, Name-B, Task-C, Hours worked-D and notes in E.
This is all working well so far.
Now onto the actual problem – I have created UserForm2 and this form is for my use only. The form include two calendars, two ListBoxes (filled from the same lists as above) and a CommandButton (Submit). The way I would like it to work is: I pick the start date, the end date, the staff and tasks I want to track and click submit. The filter sorts the data on Sheet1 with only the details between those dates, the staff selected and the task selected, it then SUMS the hours (D) and gives me that answer.
e.g. 01 Mar 17 – 20 Mar 17, Staff No 3, worked on Task 6 for 3 hours. All I need is the “3” in this case.
Is there a way of doing this? I am using Excel 2003 and have got the calendars working to output the date into Cell F1, F2 on the Filters sheet. I thought I would then be able to use these dates to further drill down but am in a bit over my head at the moment.
The code looks like this from the CommandButton (Submit)
Private Sub CommandButton2_Click()
Dim i As Integer
i = 1
While ThisWorkbook.Worksheets("Filters").Range("A" & i).Value <> ""
i = i + 1
Wend
ThisWorkbook.Worksheets("Filters").Range("F1").Value = Calendar1.Value
ThisWorkbook.Worksheets("Filters").Range("F2").Value = Calendar2.Value
End Sub
Any help is appreciated.
Cheers
