You can add two sorts of buttons to a worksheet in Microsoft Excel: choice buttons and toggle buttons. Options buttons, commonly known as radio buttons, allow you to select one item from a list. Toggle buttons can be enabled or disabled, enabling you to toggle between two states, such as on and off. After inserting a button into your spreadsheet, you can attach it to a form or ActiveX controls to execute an action when clicked. If you are a user that uses a lot of buttons to activate macros in his workbooks, and they move all over the place and resize themselves for some reason. You may be asking why this is happening and what he can do to stop it. You may want the buttons to remain on the sheet and not be erased. Excel, believe it or not, permits you to remove such buttons. If you don’t want them to act that way, you must take action to restrain them. Follow through for more information on ways to prevent a button being deleted in Excel.
Step by step process – How to prevent a button being deleted in Excel
- Firstly, open your Excel document.
- After that select the entire sheet by clicking on the select sheet icon.
- Right-click, then click on “Format Cells”.
- Navigate to “Protection”.
- Ensure the checkbox next to “Locked“ is unticked.
- Click on “OK”.
- Right-click on the button.
- After that click on “Format Control”.
- Navigate over to “Protection”.
- Ensure the boxes for “Locked” and “Lock text are checked”.
- Press “OK”.
- Now click on “Review”.
- Ensure the checkbox for “select locked cells is unchecked.”
- Finally, click on “OK”.
The button will remain, still clickable, however, you will not be able to edit or delete it, you can further refine the button by unprotecting the sheet until you get it perfect. You may be wondering, why not just edit the button to locked, well the format controls for the button run parallel with the controls for the sheet, if you were to not protect the sheet while the button is locked, it won’t actually do anything since formatting is still enabled as a collective. Think of it as a shape inside the container, the shape is locked into the container you cant move it however you can move and edit the container which in turn allows you to move and edit the shape, delete the container the shape gets deleted. Same example here, you must follow through to ensure the correct things are locked and unlocked.
Select the Type of Button
Select the sort of choice button you want to add. The two primary types are Form Control and ActiveX Control. Click “Option Button” from the Form Controls list to place a Form Control option button. When you hover your cursor over the buttons, their names show. Click “Option Button” from the list of ActiveX Controls to insert an ActiveX Control option button.
You are aware that you can secure a worksheet so that users are unable to erase cells. However, you must block cell deletion without employing worksheet protection. (You are not referring to the cleaning of cell contents, but to the deletion of cells such that adjacent cells must migrate to the left or to the right.) You’re wondering whether there’s a method to stop a user from removing cells without safeguarding the worksheet.
This will prevent users from deleting the button, however, the method above illustrates a scenario where the button is locked and the cells around it are unlocked which allows you to edit them.
Select a cell on your worksheet by clicking it
Select the cell on your worksheet in which you want your choice button to appear.
Customize the Button
The format or change the attributes of your button to make it do anything when clicked. To alter the attributes of a Form Control choice button, for example, right-click it and pick “Format Control” from the drop-down menu. If you used an ActiveX Control button, right-click it and choose “Properties” from the drop-down menu.
Use a VBA script to prevent the deletion of buttons in Excel
Excel VBA is a complex technology that enables you to automate Excel procedures. It may be used to automate a wide range of mundane tasks as well as more complex applications. This quick tip tutorial will show you how to access the Excel Visual Basic Editor (commonly known as the Excel VBA Editor) and start programming in Excel using VBA. You may use the VBA editor to create your own VBA code or to review previously recorded macros. Continue reading to learn more. How to Prevent the Erasure of an Excel Spreadsheet In Microsoft Excel, use the VBA Editor.
Step by step process – Use the VBA editor to prevent an Excel sheet being deleted
- Open the Excel spreadsheet.
- By clicking on the tab at the bottom of this Excel file, you may protect a certain sheet from deletion. Select View Code from the context menu when you right-click on the tab.
- In the Code box, enter the following codes. Substitute “yourpassword” with your real password.
Private Sub Worksheet_Activate()
ThisWorkbook.Protect “yourpassword”
End Sub
Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect “yourpassword”
End Sub
- To save the VB project, click the Save button or press Ctrl + S. When the following dialogue appears, select No.
In-depth – Step by step process – How to prevent a button being deleted in Excel
Step by step breakdown
- Firstly, open your Excel document.
- After that select the entire sheet by clicking on the select sheet icon.
- Right-click, then click on “Format Cells”.
- Navigate to “Protection”.
- Ensure the checkbox next to “Locked“ is unticked.
- Click on “OK”.
- Right-click on the button.
- After that click on “Format Control”.
- Navigate over to “Protection”.
- Ensure the boxes for “Locked” and “Lock text are checked”.
- Press “OK”.
- Now click on “Review”.
- Ensure the checkbox for “select locked cells is unchecked.”
- Finally, click on “OK”.
That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.