October 3, 2021

Automatically Add Value to Entry in Worksheet

How to automatically add a value to an entry in an Excel worksheet using VBA in a worksheet change event. Watch the video below:

Automatically Adding Value to Entry in Excel Worksheet

Often we can automate our calculations quickly and easily using a simple worksheet change event. Today we have learnt how to add a value like a goods and services tax to our entered data. In the process of making our work interactive we may have to enter values in an input box. But if your tax is the same for all entries the macro code will be very simple. In fact, you may not need a macro. You can calculate once manually and later do an autofill. We have described below the macro code for different scenarios.

  1. Automatic calculations using a worksheet change event when the tax value keeps on changing:

Option Explicit

‘Automatically Adding Tax to an Entry
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tax As Single
Dim question As String

question = Application.InputBox(“Do you wish to enter the tax amount? Type ‘n’to end program.”, “Question”)

If LCase(question) = “n” Or LCase(question) = “no” Then
MsgBox “Bye!”
Exit Sub
Else
tax = Application.InputBox(“Enter Tax Amount. For example, if Tax is 18% then enter 1.18.”, “Add Tax”)
End If

Set rInt = Intersect(Target, Range(“plusGST”))

If Not rInt Is Nothing Then
For Each rCell In rInt
If IsNumeric(rCell) And rCell > 0 Then
With Application
.EnableEvents = False
rCell.Offset(0, 1) = Round(rCell * tax)
.EnableEvents = True
End With
End If
Next
End If

End Sub

2. Automatic Tax calculations where tax rate does not change:

Option Explicit

‘Automatically Adding Tax to an Entry
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tax As Single
Dim question As String
tax = 1.18

Set rInt = Intersect(Target, Range(“plusGST”))

If Not rInt Is Nothing Then
For Each rCell In rInt
If IsNumeric(rCell) And rCell > 0 Then
With Application
.EnableEvents = False
rCell.Offset(0, 1) = Round(rCell * tax)
.EnableEvents = True
End With
End If
Next
End If

End Sub

3. Automatic calculations of tax for different types like Central Goods and Services Tax (CGST) and State Goods and Services Tax (SGST):

Option Explicit

‘Automatically Adding Tax to an Entry
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim question As String
Dim CGST As Single
Dim SGST As Single
CGST = 1.09
SGST = 1.09

Set rInt = Intersect(Target, Range(“plusGST”))

If Not rInt Is Nothing Then
For Each rCell In rInt
If IsNumeric(rCell) And rCell > 0 Then
With Application
.EnableEvents = False
rCell.Offset(0, 1) = Round(rCell * CGST)
rCell.Offset(0, 2) = Round(rCell * SGST)
.EnableEvents = True
End With
End If
Next
End If

End Sub

4. Calculate total values with different types of taxes and total tax due:

‘Automatically Adding Tax to an Entry
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tax As Single
Dim question As String
Dim CGST As Single
Dim SGST As Single
CGST = 1.09
SGST = 1.09
tax = 1.18

Set rInt = Intersect(Target, Range(“plusGST”))

If Not rInt Is Nothing Then
For Each rCell In rInt
If IsNumeric(rCell) And rCell > 0 Then
With Application
.EnableEvents = False
rCell.Offset(0, 1) = Round(rCell * CGST) ‘ add CGST to taxable value
rCell.Offset(0, 2) = Round(rCell * SGST) ‘ add SGST to taxable value
‘total tax
rCell.Offset(0, 3) = Round((rCell * tax) – (rCell))
.EnableEvents = True
End With
End If
Next
End If

End Sub

Further Reading:

Using the Intersect Method in Excel VBA