{"id":257,"date":"2015-11-02T09:11:19","date_gmt":"2015-11-02T09:11:19","guid":{"rendered":"http:\/\/99excel.com\/?p=257"},"modified":"2021-05-14T16:08:49","modified_gmt":"2021-05-14T16:08:49","slug":"india-salary-tax-computation-in-excel-vba","status":"publish","type":"post","link":"https:\/\/99excel.com\/home\/india-salary-tax-computation-in-excel-vba\/","title":{"rendered":"India Salary tax computation in excel vba"},"content":{"rendered":"

Below we will look at a program in Excel VBA<\/strong> that calculates the tax on an income. The following tax rates<\/strong> apply to individuals who are residents of India.<\/p>\n

 <\/p>\n

Download the Indian Salary tax computation in excel vba. Tax Computation & CTC Structure 2015-16<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

Code in open sheet<\/strong><\/p>\n

Private Sub Workbook_Open()
\nApplication.ScreenUpdating = False
\nApplication.DisplayAlerts = False
\nActiveWorkbook.Sheets(“Tax Computation & CTC Structure”).Visible = xlSheetVisible
\nActiveWorkbook.Sheets(“Welcome”).Visible = xlSheetVeryHidden
\nApplication.Calculation = xlCalculationAutomatic
\nSheets(“Tax Computation & CTC Structure”).Select
\n‘Call My_Vba.Resetall
\nActiveWindow.ScrollColumn = 1
\nActiveWindow.ScrollColumn = 1<\/p>\n

End Sub<\/p>\n

Private Sub Workbook_Activate()
\nApplication.ScreenUpdating = False
\nActiveWorkbook.Protect (“password”)
\nApplication.ScreenUpdating = False
\nEnd Sub<\/p>\n

Private Sub Workbook_BeforeClose(Cancel As Boolean)
\nApplication.ScreenUpdating = False
\nActiveWorkbook.Unprotect (“password”)
\nActiveWorkbook.Sheets(“Welcome”).Visible = xlSheetVisible
\nActiveWorkbook.Sheets(“Tax Computation & CTC Structure”).Visible = xlSheetVeryHidden
\nApplication.ScreenUpdating = False
\nApplication.DisplayAlerts = False
\nActiveWorkbook.Protect (“password”)
\nThisWorkbook.Close savechanges:=True
\nApplication.ScreenUpdating = False
\nEnd Sub<\/p>\n

 <\/p>\n

\n

 <\/p>\n

following code in work sheet<\/p>\n

 <\/p>\n

Private Sub Worksheet_Change(ByVal Target As Range)
\nDim KeyCells, KeyCells1, KeyCells2, KeyCells3, KeyCells4 As Range<\/p>\n

Set KeyCells = Range(“Annual_CTC”)
\nSet KeyCells1 = Range(“Bonus_Ap”)
\nSet KeyCells2 = Range(“Basic,Hra,Conv,metronmetro”)
\nSet KeyCells3 = Range(“Medical,lta,veh,driv,pd”) ‘enta
\nSet KeyCells4 = Range(“var_paya”)
\nSet KeyCells5 = Range(“PF_Ap,ESI_Ap,Gratuity_Ap,Bonus_Ap”)
\nSet KeyCells6 = Range(“Renta”)<\/p>\n

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
\nCall Missmatch
\nIf Range(“Spl_all”).Value < 0 Then
\nRange(“lta”).Value = 0
\nRange(“veh”).Value = 0
\nRange(“driv”).Value = 0
\nElse: End If<\/p>\n

ElseIf Not Application.Intersect(KeyCells1, Range(Target.Address)) Is Nothing Then
\nApplication.DisplayAlerts = False
\nIf Range(“Bonus_Ap”).Value = “Yes” Then
\nRange(“bonus_amt”).Formula = “=IF(Annual_CTC>0,IF(SUM(reimb_ff)>0,0,10000),0)”
\nElse
\nRange(“bonus_amt”).Value = 0<\/p>\n

End If<\/p>\n

ElseIf Not Application.Intersect(KeyCells2, Range(Target.Address)) Is Nothing Then
\nApplication.DisplayAlerts = False
\nCall Missmatch<\/p>\n

ElseIf Not Application.Intersect(KeyCells3, Range(Target.Address)) Is Nothing Then
\nApplication.DisplayAlerts = False
\nCall Missmatch
\nRange(“lta”).Select<\/p>\n

ElseIf Not Application.Intersect(KeyCells4, Range(Target.Address)) Is Nothing Then
\nApplication.DisplayAlerts = False
\nCall Missmatch
\nRange(“comp_med”).Select<\/p>\n

ElseIf Not Application.Intersect(KeyCells5, Range(Target.Address)) Is Nothing Then
\nApplication.DisplayAlerts = False
\nCall Missmatch
\nRange(“PF_Ap”).Select<\/p>\n

ElseIf Not Application.Intersect(KeyCells6, Range(Target.Address)) Is Nothing Then
\nApplication.DisplayAlerts = False<\/p>\n

If Range(“Renta”).Value > 0 Then<\/p>\n

If Range(“Renta”).Value > 8333 Then
\nMsgBox (“Monthly rent is above Rs. 8333\/-, You required to report the PAN of the landlord to the employer at the end of year.”)
\nRange(“rentlocation”).Value = “Delhi”
\nRange(“from”).Formula = “=MAX(” & “01\/04\/2014” & “,Effectivedate)”
\nRange(“to”).Value = “31\/03\/2016”
\nElse: End If
\nRange(“rentlocation”).Value = “Delhi”
\nRange(“from”).Formula = “=MAX(” & “01\/04\/2014” & “,Effectivedate)”
\nRange(“to”).Value = “31\/03\/2016”
\nRange(“Renta”).Select
\nElse: End If
\nElse: End If
\nEnd Sub<\/p>\n

\n

Sub Resetall()
\nOn Error Resume Next
\nSheets(“Tax Computation & CTC Structure”).Unprotect Password:=”password”
\nApplication.ScreenUpdating = False
\nApplication.DisplayAlerts = False<\/p>\n

Range(“Annual_CTC”).Value = “0”<\/p>\n

Range(“Basic”).ClearContents
\nRange(“Basic”).Formula = “=ROUND(Annual_CTC*20%,0)”<\/p>\n

Range(“reimb_amt”).Value = “0”
\nRange(“itamt”).Value = “0”
\nRange(“var_paya”).Value = “0”<\/p>\n

Range(“Conv”).ClearContents
\nRange(“Conv”).Formula = “=IF(veh>0,0,IF(Annual_CTC>0,19200,0))”<\/p>\n

Range(“Medical”).ClearContents
\nRange(“Medical”).Formula = “=IF(Annual_CTC>0,15000,0)”<\/p>\n

Range(“lta”).ClearContents
\nRange(“lta”).Formula = “=IF(Annual_CTC>0,60000,0)”<\/p>\n

Range(“veh”).ClearContents
\nRange(“veh”).Formula = “=IF(Annual_CTC>0,96000,0)”<\/p>\n

Range(“driv”).ClearContents
\nRange(“driv”).Formula = “=IF(Annual_CTC>0,60000,0)”<\/p>\n

Range(“rentlocation”).ClearContents
\nRange(“from”).ClearContents
\nRange(“to”).ClearContents<\/p>\n

If Range(“Bonus_Ap”).Value = “Yes” Then
\nRange(“bonus_amt”).Formula = “=IF(Annual_CTC>0,IF(SUM(reimb_ff)>0,0,10000),0)”
\nElse
\nRange(“bonus_amt”).Value = 0
\nEnd If<\/p>\n

Range(“us8c”).Value = “0”
\nRange(“medis”).Value = “0”
\nRange(“medps”).Value = “0”
\nRange(“usE”).Value = “0”
\nRange(“usU”).Value = “0”
\nRange(“usdd”).Value = “0”
\nRange(“usddb”).Value = “0”
\nRange(“usccd”).Value = “0”<\/p>\n

Range(“Renta”).Value = “0”
\nRange(“ptax”).Value = “0”
\nRange(“prv_sal”).Value = “0”
\nRange(“inc_other”).Value = “0”
\nRange(“Deducted_tax”).Value = “0”<\/p>\n

Range(“medbill”).Formula = “=C14”
\nRange(“medbill”).Select
\nSelection.Copy
\nRange(“billsamt”).Select
\nActiveSheet.Paste
\nApplication.CutCopyMode = False
\nRange(“medbill”).Select<\/p>\n

Range(“Emp_name”).Value = “”
\nRange(“DSG_name”).Value = “”<\/p>\n

On Error Resume Next<\/p>\n

Sheets(“Tax Computation & CTC Structure”).Protect Password:=”password”
\nApplication.ScreenUpdating = False
\nEnd Sub<\/p>\n

Sub Missmatch()
\nOn Error Resume Next
\nApplication.ScreenUpdating = False
\nSheets(“Tax Computation & CTC Structure”).Unprotect Password:=”password”
\nRange(“Spl_all”).Select
\nSelectionvalue = 0
\nRange(“diffrence”).Select
\nSelection.Copy
\nRange(“Spl_all”).Select
\nSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
\n:=False, Transpose:=False
\nSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
\n:=False, Transpose:=False
\nSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
\n:=False, Transpose:=False
\nSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
\n:=False, Transpose:=False
\nSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
\n:=False, Transpose:=False
\nApplication.CutCopyMode = False
\nSheets(“Tax Computation & CTC Structure”).Protect Password:=”password”
\nOn Error Resume Next
\nApplication.ScreenUpdating = False
\nEnd Sub<\/p>\n

Sub PrintSheet()
\nApplication.ScreenUpdating = False
\nDim f As Integer, t As Integer, Message, Ans
\nApplication.ScreenUpdating = False<\/p>\n

If Range(“Emp_name”).Value = “” Then
\nRange(“Emp_name”).Select
\nMsgBox (“Employee Name Shoud not be Blank”)
\nExit Sub
\nEnd If<\/p>\n

If Range(“DSG_name”).Value = “” Then
\nRange(“DSG_name”).Select
\nMsgBox (“Employee Designation Shoud not be Blank”)
\nExit Sub
\nEnd If<\/p>\n

If Range(“Annual_CTC”).Value < 1 Then
\nRange(“Annual_CTC”).Select
\nMsgBox (“Annual_CTC is not Correct”)
\nExit Sub
\nEnd If<\/p>\n

If Range(“Spl_all”).Value < 1 Then
\nRange(“Spl_all”).Select
\nMsgBox (“CTC Structure values are not Correct”)
\nExit Sub
\nEnd If<\/p>\n

Application.ScreenUpdating = False<\/p>\n

Ans = MsgBox(“Are you sure want to Print ?”, vbYesNo + vbExclamation, “Confirmation – Yes No”)
\nIf Ans = vbNo Then
\nExit Sub
\nEnd If
\nActiveSheet.PrintOut<\/p>\n

Application.ScreenUpdating = False<\/p>\n

End Sub<\/p>\n

\n

 <\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Below we will look at a program in Excel VBA that calculates the tax on an income. The following tax rates apply to individuals who are residents of India.   Download the Indian Salary tax computation in excel vba. Tax Computation & CTC Structure 2015-16     Code in open sheet Private Sub Workbook_Open() Application.ScreenUpdating = False … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-257","post","type-post","status-publish","format-standard","hentry"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/comments?post=257"}],"version-history":[{"count":1,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/257\/revisions"}],"predecessor-version":[{"id":870,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/257\/revisions\/870"}],"wp:attachment":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/media?parent=257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/categories?post=257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/tags?post=257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}