Method 1 – Increase Cell Value by Toggle Button
Steps:
- Insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Rename the toggle button according to your desire. We keep the toggle button name as $50 Salary Increase.
- Double-click on the toggle button to open the Visual Basic Editor box.
- Write the following VBA code in that empty box.
Private Sub ToggleButton1_Click()
Dim i As Integer
For i = 5 To 14
Cells(i, 3).Value = Cells(i, 3).Value + 50
Next i
End Sub- Press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Click once on the $50 Salary Increase toggle button, and you will notice each cell value will increase by $50 within a second.
Breakdown of the VBA Code
Dim i As Integer
Declare an integer variable which is i.
For i = 5 To 14
Cells(i, 3).Value = Cells(i, 3).Value + 50
Next iApply a For loop to add 50 with the values of every cell from rows 5 to 14.
Method 2 – Decrease Cell Value Through Toggle Button
Steps:
- Insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Rename the toggle button according to your desire. We keep the toggle button name as Decrease Value.
- Double-click on the toggle button to open the Visual Basic Editor box.
- Write the following VBA code in that empty box.
Private Sub ToggleButton1_Click()
Dim i As Integer
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value - 50
Next i
End Sub- Press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Click once on the Decrease Value toggle button, and you will notice each cell value of column D replaced with the new value.
Breakdown of the VBA Code
Dim i As IntegerDeclare an integer variable which is i
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value - 50
Next i
Apply a For loop to replace all values of column 4 with column 3 after deducting 50 from the existing value.
Method 3 – Add and Change Cell Value at Same Time
Steps:
- Insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Rename the toggle button according to your desire. We keep the toggle button name as Add $50.
- Double-click on the toggle button to open the Visual Basic Editor box.
- Write down the following VBA code in that empty box.
Private Sub ToggleButton1_Click()
Dim i As Integer
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value + 50
Next i
End Sub- Press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Click once on the Add $50 toggle button, and you will get the result.
Breakdown of the VBA Code
Dim i As IntegerDeclare an integer variable which is i
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value + 50
Next iApply a For loop to replace all values of column 4 with column 3 after adding 50 with each cell value.
Method 4 – Multiplying a Cell Value Through Toggle Button
Steps:
- Insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Rename the toggle button according to your desire. We keep the toggle button name as the 10% Increment.
- Double-click on the toggle button to open the Visual Basic Editor box.
- Write down the following VBA code in that empty box.
Private Sub ToggleButton1_Click()
Dim i As Integer
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value * 1.1
Next i
End Sub
- Press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Click once on the 10% Increment toggle button, and you will get the result.
Breakdown of the VBA Code
Dim i As IntegerDeclare an integer variable which is i
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value * 1.1
Next i
Apply a For loop to replace all values of column 4 with column 3 after multiplying 10% increment with every value.
Method 5 – Change Cell Value Based on Criteria
Steps:
- Insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Rename the toggle button according to your desire. We keep the toggle button name as the Apply Criteria.
- Double-click on the toggle button to open the Visual Basic Editor box.
- Write the following VBA code in that empty box.
Private Sub ToggleButton1_Click()
Dim i As Integer
For i = 5 To 14
If Cells(i, 3).Value = 0 Then
Cells(i, 3).Value = Cells(i, 3).Value + 1000
Else
Cells(i, 3).Value = Cells(i, 3).Value + 100
End If
Next i
End Sub- Press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Click once on the Apply Criteria toggle button, and you will figure out the final result.
Breakdown of the VBA Code
Dim i As Integer
Declare an integer variable which is i.
For i = 5 To 14
If Cells(i, 3).Value = 0 Then
Cells(i, 3).Value = Cells(i, 3).Value + 1000
Else
Cells(i, 3).Value = Cells(i, 3).Value + 100
End If
Next iApply a For loop to replace all values of column 3. Moreover, inside the For loop, we use a conditional If argument to check whether the value is 0 or not, If the cell value is 0, we will get 1000. It will add 100 to the existing value.
Method 6 – Deleting Cell Value by Toggle Button
Steps:
- Insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Rename the toggle button according to your desire. We keep the toggle button name as the Delete Value.
- Double-click on the toggle button to open the Visual Basic Editor box.
- Write the following VBA code in that empty box.
Private Sub ToggleButton1_Click()
ActiveCell.ClearContents
End Sub- Press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Select a cell and click once on the Delete Value toggle button.
- You will see the value is removed from the cell.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Get FREE Advanced Excel Exercises with Solutions!
