How to Change Cell Value Using Toggle Button in Excel

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.

Insert Toggle Button from the Insert tab to change cell value

  • 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.

Inserted toggle button in Excel sheet

  • After that, right-click on the toggle button and select the ToggleButton Object > Edit option to rename it.

Changing toggle button name to change cell value

  • In our case, we keep the name ToggleButton.

Renamed toggle button to change cell value in Excel sheet

  • 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.

Choose ToggleButton option to insert toggle button

  • Then, rename the toggle button according to your desire. We keep the toggle button name as $50 Salary Increase.

Inserted toggle button to change cell value

  • 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.

Increase Cell Value by Toggle Button to Change Cell Value by Toggle Button

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.

Choose ToggleButton option to insert toggle button

  • Now, rename the toggle button according to your desire. We keep the toggle button name as Decrease Value.

Using decrease value toggle button to change cell 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.

Decrease Cell Value Through Toggle Button to Change Cell Value by Toggle Button

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.

Choose ToggleButton option to insert toggle button

  • After that, rename the toggle button according to your desire. We keep the toggle button name as Add $50.

Insert Add $50 toggle button to change cell value

  • 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.

Add and Change Cell Value at Same Time to Change Cell Value by Toggle Button

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


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.

Choose ToggleButton option to insert toggle button

  • Next, rename the toggle button according to your desire. We keep the toggle button name as the 10% Increment.

Insert 10% Increment toggle button to change the cell value

  • 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.

Multiplying a Cell Value Through Toggle Button to Change Cell Value by Toggle Button

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.

Choose ToggleButton option to insert toggle button

  • Now, rename the toggle button according to your desire. We keep the toggle button name as the Apply Criteria.

Insert Apply Criteria toggle button to change cell value

  • 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.

Change Cell Value Based on Criteria to Change Cell Value by Toggle Button

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.

Choose ToggleButton option to insert toggle button

  • Then, rename the toggle button according to your desire. We keep the toggle button name as the Delete Value.

Add Delete Value toggle button to change 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.

Deleting Cell Value by Toggle Button to Change Cell Value by Toggle Button

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

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo