Combine staff leave tracker with their leave entitlement in Excel

• We have three sheets in the excel sheet
• The first sheet is the staff in a list against a yearly schedule
• On the second sheet we have the leave entitlement calculations
• We have employee names
• Start date and the end date is the first of January 2020
• Using this formula I have calculated the number of complete years as of January 1
• Using the values calculate I have used Vlookup and this table of hours to calculate how many hour of Vacation they have
• Now in this organization everybody is given 35 of personal time and 70 hours of sick time
• I have linked all these values to fields on the first page.
• For example on the number of sick hours subtracting the count of “S” that appears on the with in the year for that individual

code
Private Sub CommandButton1_Click()
Dim name As String
Dim leave As String
Dim rng As Range
Dim rownumber, monthmove As Integer
Dim Lstart, LEnd, difference, lnext As Integer

Dim x As Long

If TextBox1.Text = “” Then
MsgBox “Enter Name”
End If
‘name = TextBox1

name = Trim(TextBox1.Text)
Set rng = Sheet1.Columns(“A:A”).Find(What:=name, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
MsgBox “Name not found”
Else
rownumber = rng.Row

End If
If cbleave.Value = “no pay” Then
leave = “np”
End If
If cbleave.Value = “Vacation” Then
leave = “V”
End If
If cbleave.Value = “Sick” Then
leave = “S”
End If
If cbleave.Value = “personal” Then
leave = “P”
End If

If cbmonths.Value = “January” Then
monthmove = 4
End If
If cbmonths.Value = “February” Then
monthmove = 35
End If

If cbmonths.Value = “March” Then
monthmove = 64
End If

If cbmonths.Value = -1 Then
MsgBox “No month Selected”
End If
‘MsgBox monthmove

Lstart = Trim(TextBox2.Text) + monthmove
LEnd = Trim(TextBox3.Text) + monthmove

Sheet1.Cells(rownumber, Lstart).Value = leave
Sheet1.Cells(rownumber, LEnd).Value = leave
difference = LEnd – Lstart
If difference(greater then symbol) 1 Then
lnext = Lstart
Do While lnext (less than symbol) LEnd
Sheet1.Cells(rownumber, lnext).Value = leave
lnext = lnext + 1
If lnext = LEnd Then
GoTo ende
End If
Loop
End If

ende:

End Sub

Private Sub CommandButton2_Click()
TextBox1.Text = “”
cbmonths.Value = “”
cbleave.Value = “”
TextBox2 = “”
TextBox3 = “”
End Sub

For more help visit my website or email me at easyexcelanswers@gmail.com.

Contact me regarding customizing this template for your needs.

Excel one-on-one on-line training available. Email me to arrange.

I am able to provide online help on your computer at a reasonable rate.

Check out my next one-hour Excel Webinar

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

Follow me on Facebook

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.

Watch more new videos about Excel Office | Synthesized by Mindovermetal English