I have the following code but i want this code to run based on the selection from the dropdown list.I have created the operations to be performed as individual functons.Please help.I have attached my excel file
Code
- Global iheadcount As Integer
- Function headcount() As Double
- iheadcount = ActiveWorkbook.Worksheets("Timesheet").Range("B4", Worksheets("Timesheet").Range("B4").End(xlDown)).Rows.count
- headcount = iheadcount
- End Function
- Function leavecount() As Double
- Dim ileavehours As Double
- Dim fleavecount As Integer, hleavecount As Integer
- Dim i As Integer, j As Integer, k As Integer, l As Integer
- i = 4
- j = 6
- k = 25
- l = 10
- fleavecount = WorksheetFunction.CountIf(Worksheets("Timesheet").Range(Sheets("Timesheet").Cells(i, j), Sheets("Timesheet").Cells(k, l)), "L")
- hleavecount = WorksheetFunction.CountIf(Worksheets("Timesheet").Range(Sheets("Timesheet").Cells(i, j), Sheets("Timesheet").Cells(k, l)), "HL")
- ileavehours = ((fleavecount * 9) + (hleavecount * 4.5))
- leavecount = ileavehours
- End Function
- Function totalhours() As Double
- Dim itotalhours As Double
- Dim i As Integer, j As Integer
- i = 11
- j = 2
- itotalhours = (iheadcount * 9 * 5) - Cells(i, j)
- totalhours = itotalhours
- End Function
- Function billedhours() As Double
- Dim ibilledhours As Double
- Dim i As Integer, j As Integer, k As Integer, l As Integer
- i = 4
- j = 6
- k = iheadcount + 3
- l = 10
- ibilledhours = WorksheetFunction.Sum(Sheets("Timesheet").Range(Sheets("Timesheet").Cells(i, j), Sheets("Timesheet").Cells(k, l)))
- Sheets("Dashboard").Range("B9") = ibilledhours
- billedhours = ibilledhours
- End Function
- Function idlehours()
- Dim iidlehours As Double
- Dim i As Integer, j As Integer, k As Integer, l As Integer
- i = 8
- j = 2
- k = 9
- l = 2
- iidlehours = Cells(i, j) - Cells(k, l)
- idlehours = iidlehours
- End Function
- Sub calculate()
- Dim iheadcount, itotalhours, iweekNum As Integer
- Dim ileavehours As Double
- iheadcount = headcount()
- Sheets("Dashboard").Range("B7") = iheadcount
- itotalhours = totalhours()
- Sheets("Dashboard").Range("B8") = itotalhours
- weekNum = 1
- ibilledhours = billedhours()
- Sheets("Dashboard").Range("B9") = ibilledhours
- iidlehours = idlehours()
- Sheets("Dashboard").Range("B10") = iidlehours
- ileavehours = leavecount
- Sheets("Dashboard").Range("B11") = ileavehours
- End Sub