macro help

[ganeshpoojary05]

hi everyone, i need a help to create a macro.

for example:
i have a leave tracker report where i have 400 employees. so i want to create a macro in which only 23 employees can take a leave on a day, and agent should not excced 21 leaves in a year.
1.if the leave count exceeds 23 than it should prompt with a message "Daily Leave quota exhausted" and it should not allow me enter more leaves.
2. if the leave count exceeds 21 in a year than it should prompt a message as "Agent has exhausted his leave balance"
3.if the above 2 conditions are ok than it should prompt a message as "Leave Validated".

Please note that the Leave calender is for 12 months (365 days).

 

[stnkynts]

Please provide some cell references or a picture of your workbook so we have a better idea on what is going on. The more specific you can be the better.

 

[ganeshpoojary05]

example:

date starts from G4 to NG4(1st jan to 31st Dec) till G403 to NG403(total 400 employees).

 

[stnkynts]

Like this?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tColumn As Long, tRow As Long, dailyCount As Long, empCount As Long
Dim Msg As String
 
If Not Intersect(Target, Range("G4:NG403")) Is Nothing Then
    tColumn = Target.Column
    tRow = Target.Row
    dailyCount = Application.WorksheetFunction.Sum(Range(Cells(4, tColumn), Cells(403, tColumn)))
    empCount = Application.WorksheetFunction.Sum(Range(Cells(tRow, 7), Cells(tRow, 371)))
        If dailyCount > 23 Then
            Msg = MsgBox("Daily Leave quota exhausted", vbCritical, "Error")
        ElseIf empCount > 21 Then
            Msg = MsgBox("Agent has exhausted his leave balance", vbCritical, "Error")
        ElseIf dailyCount < 24 And empCount < 22 Then
            Msg = MsgBox("Leave Validated", vbOKOnly, "Success")
        End If
End If
 
End Sub

Put in Worksheet Change

 

[ganeshpoojary05]

one change, i'm using a command button on clicking on my command button the same macro should run. thanks!!!

 

[klarowe]

If you only want it controlled by the button then you can create a button and copy/paste all but the first and last lines of the code stnkynts posted.

Or if you want it controlled by button or by worksheet change, you can post the macro that stnkyts posted into a module with some name (ie Sub Leave). Then create a form button (not activeX) and assign it to that macro and also add

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Run "Leave"
End Sub

to the worksheet

 

[ganeshpoojary05]

im sorry, im not getting your point. can u explain me stepwise. thanks!!!

 

[klarowe]

Do you want to run the macro ONLY by the command button or do you want it to run automatically whenever a change is made in one of those cells as well as by a command button?

 

[ganeshpoojary05]

hi,

i need to run the macro by command button, but when as per the above condition if the leaves for the day exceeds 23 then it should not allow me to add more leaves in the tracker. thanks!!!

 

[klarowe]

Ok, so it sounds like you need it in both.
So first, open one of the modules in VBA and copy/paste this code:

Option Explicit
Sub Leave()
Dim tColumn As Long, tRow As Long, dailyCount As Long, empCount As Long
Dim Msg As String
 
If Not Intersect(Target, Range("G4:NG403")) Is Nothing Then
    tColumn = Target.Column
    tRow = Target.Row
    dailyCount = Application.WorksheetFunction.Sum(Range(Cells(4, tColumn), Cells(403, tColumn)))
    empCount = Application.WorksheetFunction.Sum(Range(Cells(tRow, 7), Cells(tRow, 371)))
        If dailyCount > 23 Then
            Msg = MsgBox("Daily Leave quota exhausted", vbCritical, "Error")
        ElseIf empCount > 21 Then
            Msg = MsgBox("Agent has exhausted his leave balance", vbCritical, "Error")
        ElseIf dailyCount < 24 And empCount < 22 Then
            Msg = MsgBox("Leave Validated", vbOKOnly, "Success")
        End If
End If
 
End Sub

Then under the specific worksheet VBA page type this code:

Private Sub Worksheet_Change(ByVal Target As Range)
     Application.run "Leave"
End Sub

And finally, create a command button, double click on the button and enter

Application.run "Leave"

As the command button code.

That should do what you want. If not, then its beyond my limited scope and someone else may have to chime in.