How to Delete a Command Button in Excel VBA
To delete a command button in Excel VBA, you can use the Delete
method of the command button’s parent object, which is usually a worksheet, userform, or a specific range on a worksheet. Here’s how you can delete a command button using Excel VBA:
' Assuming the command button is on a worksheet named "Sheet1"
Dim btn As Object
Set btn = Sheet1.Shapes("ButtonName") ' Replace "ButtonName" with the actual name of your command button
btn.Delete
In the above code, we first declare a variable btn
of type Object
to represent the command button. We then use the Set
statement to assign the command button object to our variable. The Sheet1.Shapes("ButtonName")
represents the command button on the worksheet.
After assigning the command button object to the variable, we can use the Delete
method to delete the command button from the worksheet. The Delete
method is called on the command button object, which removes it from the worksheet.
It’s important to note that the above code assumes that the command button has a specific name (“ButtonName” in this case). If your command button doesn’t have a name, you can assign it a name using the Properties
window in the Excel VBA editor.
Example:
Let’s say we have a command button named “DeleteButton” on a worksheet named “Sheet1”. Here’s an example code that deletes the command button when it’s clicked:
Private Sub DeleteButton_Click()
Dim btn As Object
Set btn = Sheet1.Shapes("DeleteButton")
btn.Delete
End Sub
In the above example, we declare a Private Sub
procedure named DeleteButton_Click()
, which runs when the command button named “DeleteButton” is clicked. Inside the procedure, we use the same code as discussed earlier to delete the command button from the worksheet.