How to Change Cell Value Using Toggle Button in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

How to Insert Toggle Button in Excel

6 Suitable Examples to Change Cell Value Using Toggle Button in Excel

1. Increase Cell Value by Toggle Button

2. Decrease Cell Value Through Toggle Button

3. Add and Change Cell Value at Same Time

4. Multiplying a Cell Value Through Toggle Button

5. Change Cell Value Based on Criteria

6. Deleting Cell Value by Toggle Button

Conclusion

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Change Cell Value by Toggle Button.xlsm

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.

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.

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.

Similar Readings

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.

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.

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.

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!

How to Change Cell Value Using Toggle Button in Excel

Download Practice Workbook

How to Insert Toggle Button in Excel

6 Suitable Examples to Change Cell Value Using Toggle Button in Excel

1. Increase Cell Value by Toggle Button

2. Decrease Cell Value Through Toggle Button

3. Add and Change Cell Value at Same Time

4. Multiplying a Cell Value Through Toggle Button

5. Change Cell Value Based on Criteria

6. Deleting Cell Value by Toggle Button

Conclusion

Related Articles

Soumik Dutta

Excel VBA to Copy Without Clipboard (3 Easy Ways)

Excel VBA to Show Message on Status Bar

[Fixed!] VBA Loop Without Do Error (3 Possible Reasons)

Excel VBA DoEvents and Wait Properties

Leave a reply Cancel reply