if checkbox is check then calculate 2%tax if uncheck then Calculate 4% tax Can you Pls.. Help me for this.
Thanks a lot in Advance.
Best of LuckIn EXCEL i want to Use Checkbox to Calculate Percentage?
Insert a checkbox onto your Sheet from the Control Toolbox. Make sure the Design tool is switched on, then double click the checkbox. This will create the subroutine in VBA for the sheet. Modify the Sub to ReCalculate when the box is clicked
Private Sub CheckBox1_Change()
Application.CalculateFullRebuild
End Sub
Then write a Macro Function to check the value of the checkbox and return the calculation
Public Function GetTax(ByVal CellValue As Double) As Double
Const TaxRate = 4.0
Dim Calculation As Double
If Sheets("Sheet1").CheckBox1.Value = False Then
Calculation = 0
Else
Calculation = CellValue / TaxRate
End If
GetTax = Calculation
End Sub
A1 = CheckBox1
B1 = Value
C1 = Tax Calculation
In the cell where you want the calculation to to put the formula, in this example C1
=GetTax($B1)In EXCEL i want to Use Checkbox to Calculate Percentage?
You don't macros to do such simple things!!!
You can use one of the Forms controls that is called "CheckBox"
BAsed on your Excel version do this:
In Excel 2003 or earlier:
- View %26gt; Toolbars %26gt; Form toolbar
- Draw a checkbox
In Excel2007:
- Office logo %26gt; Excel Options %26gt; Popular %26gt; Show Developer toolbar
- Developer bar %26gt; Insert %26gt; Form %26gt; Checkbox
- Draw it
In both after you draw the checkbox:
- Right click %26gt; Format Control %26gt; Cell link
- Select cell, make it C2
- Ok
- In C3 paste this
=IF( C1, 0.02, 0.04)
- Now cell C3 will give you the tax % based on the checkbox, so all you need to paste any cell
=A1*C3
Assuming A1 has the number you want to multiply by tax%
VBAXLMan is here to feed your Excel needs
----------------------- Edit ---------------------
I am sorry, the function in C3 should be
=IF( C2, 0.02, 0.04)
Instead of
=IF( C1, 0.02, 0.04)
I am very sorry about this
----------------------- Edit ---------------------
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment