Sometimes, we need to change the cell value using the toggle button of Microsoft Excel. Using the Excel toggle button, we can do several types of operations. In this article, we are to demonstrate to you 6 suitable examples to change the cell value by the toggle button in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
How to Insert Toggle Button in Excel
First, we will show you how to insert a toggle button in your Excel worksheet. The steps to insert a toggle button are given below:
📌 Steps:
- First of all, go to the Developer tab. If you don’t have the Developer tab, enable the Developer tab from Excel Options.
- Now, click on the drop-down arrow of the Insert command from the Controls group and choose the Toggle Button from the ActiveX Controls section.
- You will notice that your mouse cursor will show in a different format.
- Then, click on anywhere and drag your mouse to insert the toggle button.
- You will see a toggle button will appear on your Excel sheet.
- After that, right-click on the toggle button and select the ToggleButton Object > Edit option to rename it.
- In our case, we keep the name ToggleButton.
- Afterward, double-click on the toggle button to launch the visual basic window.
- Now, write down the required code inside the Private Sub.
- Finally, press ‘Ctrl+S’ to save and assign the code under the toggle button.
- Our toggle button insertion process is completed.
Thus, we can say that our working procedure works perfectly, and we are able to insert a toggle button to change the cell value in Excel.
6 Suitable Examples to Change Cell Value Using Toggle Button in Excel
To demonstrate the examples, we consider the previous dataset of 10 employees of an organization. Their name and salary are in columns B and C, respectively.
📚 Note:
All the operations of this article are accomplished by using the Microsoft Office 365 application.
1. Increase Cell Value by Toggle Button
In the first example, we are going to add value to our existing cell value. The steps to complete the process are given as follows:
📌 Steps:
- At first, insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Then, rename the toggle button according to your desire. We keep the toggle button name as $50 Salary Increase.
- After that, double-click on the toggle button to open the Visual Basic Editor box.
- Now, 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, 3).Value = Cells(i, 3).Value + 50
Next i
End Sub
- Next, press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Afterward, click once on the $50 Salary Increase toggle button, and you will notice each cell value will increase by $50 within a second.
Hence, we can say that our VBA code works perfectly, and we are able to change the cell value by the toggle button in Excel.
🔎 Breakdown of the VBA Code
Dim i As Integer
First, we declare an integer variable which is i
For i = 5 To 14
Cells(i, 3).Value = Cells(i, 3).Value + 50
Next i
Then, we apply a For loop to add 50 with the values of every cell from rows 5 to 14.
Read More: How to Change Color of Toggle Button When Pressed in Excel
2. Decrease Cell Value Through Toggle Button
In this example, we will modify the cell value using the toggle button. We have to show the salary values in column D after deducting $50 from the salary value of column C.
The steps to finish the procedure are given below:
📌 Steps:
- Firstly, insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Now, rename the toggle button according to your desire. We keep the toggle button name as Decrease Value.
- Then, double-click on the toggle button to open the Visual Basic Editor box.
- After that, 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.
- At last, click once on the Decrease Value toggle button, and you will notice each cell value of column D replaced with the new value.
Therefore, we can say that our VBA code works effectively, and we are able to change the cell value by the toggle button in Excel.
🔎 Breakdown of the VBA Code
Dim i As Integer
First, we declare an integer variable which is i
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value - 50
Next i
Next, we apply a For loop to replace all values of column 4 with column 3 after deducting 50 from the existing value.
Read More: How to Create & Apply Option Button Click Event in Excel VBA
3. Add and Change Cell Value at Same Time
In the following example, we are going to add a value with our existing value and then replace the value with the toggle button. We have empty cells in column D and we will replace them with the value of the salary value in column C and the summation of $50.
The steps to accomplish the example are shown as follows:
📌 Steps:
- First of all, insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- After that, rename the toggle button according to your desire. We keep the toggle button name as Add $50.
- Now, double-click on the toggle button to open the Visual Basic Editor box.
- Then, 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
- Afterward, press ‘Ctrl+S’ to save the code.
- Close the Editor box.
- Finally, click once on the Add $50 toggle button, and you will get the result.
So, we can say that our VBA code works precisely, and we are able to change the cell value by the toggle button in Excel.
🔎 Breakdown of the VBA Code
Dim i As Integer
First, we declare an integer variable which is i
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value + 50
Next i
Then, we apply a For loop to replace all values of column 4 with column 3 after adding 50 with each cell value.
Read More: How to Add Button in Excel (3 Handy Ways)
Similar Readings
- How to Make a Calculate Button in Excel (with Easy Steps)
- How to Use VBA Code for Submit Button in Excel (with Easy Steps)
- Print to PDF Using Macro Button in Excel (5 Macro Variants)
- How to Hide Columns with Button in Excel (4 Suitable Methods)
- Excel Button to Print Specific Sheets (With Easy Steps)
4. Multiplying a Cell Value Through Toggle Button
Now, we will demonstrate some multiplication operations to change the cell value with the toggle button. We have empty cells in column D and we will replace them with the value of the salary value in column C with a 10% of salary increment.
The steps to finish the procedure are explained below:
📌 Steps:
- First, insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Next, rename the toggle button according to your desire. We keep the toggle button name as the 10% Increment.
- Afterward, 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.
- Then, close the Editor box.
- Now, click once on the 10% Increment toggle button, and you will get the result.
Thus, we can say that our VBA code works properly, and we are able to change the cell value by the toggle button in Excel.
🔎 Breakdown of the VBA Code
Dim i As Integer
First, we declare an integer variable which is i
For i = 5 To 14
Cells(i, 4).Value = Cells(i, 3).Value * 1.1
Next i
After that, we apply a For loop to replace all values of column 4 with column 3 after multiplying 10% increment with every value.
Read More: How to Add Up and Down Buttons in Excel (2 Suitable Examples)
5. Change Cell Value Based on Criteria
Here, we are going to change the cell value with the toggle button based on criteria. We are going to provide $1000 to those who have no income and $100 to others.
The steps to complete the example are explained as follows:
📌 Steps:
- At first, insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Now, rename the toggle button according to your desire. We keep the toggle button name as the Apply Criteria.
- Then, 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
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
- After that, press ‘Ctrl+S’ to save the code.
- Next, close the Editor box.
- In the end, click once on the Apply Criteria toggle button, and you will figure out the final result.
At last, we can say that our VBA code works perfectly, and we are able to change the cell value by the toggle button in Excel.
🔎 Breakdown of the VBA Code
Dim i As Integer
First, we 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 i
Next, we apply 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. Otherwise, it will add 100 to the existing value.
Read More: How to Clear Cells in Excel with Button (with Detailed Steps)
6. Deleting Cell Value by Toggle Button
In the last example, we will delete the cell value by the toggle button. The steps to complete the example are described below:
📌 Steps:
- Firstly, insert a toggle button from the drop-down arrow of the Insert option of the Controls group, located in the Developer tab.
- Then, rename the toggle button according to your desire. We keep the toggle button name as the Delete Value.
- Afterward, double-click on the toggle button to open the Visual Basic Editor box.
- Now, write down 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.
- Thus, select a cell and click once on the Delete Value toggle button.
- You will see the value is removed from the cell.
Finally, we can say that our VBA code works successfully, and we are able to change the cell value by the toggle button in Excel.
Read More: How to Remove a Form Control in Excel (3 Quick Ways)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to change cell value by the toggle button in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- How to Set Option Button Value in Excel VBA (4 Easy Steps)
- Insert Excel VBA Radio Button Input Box (3 Easy Methods)
- How to Create Button Without Macro in Excel (3 Easy Ways)
- Add Radio Buttons in Excel (2 Effective Ways)
- How to Create Button to Link to Another Sheet in Excel (3 Ways)
- How to Group Radio Buttons in Excel (2 Simple Methods)