Hi iam not able to write a macro which triggers the macro based on the selection made from the dropdown list
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