Clear contents button

Microsoft Excel 2007
June 27, 2009 at 02:05:21
Specs: Windows Vista
Can anyone tell me how to create a button in my Excel spreadsheet that when pushed, clears the text from a cell range called "delete"?

See More: Clear contents button

Report •

June 27, 2009 at 07:57:17
There's a big difference between your subject line and the question in your post.

"Clear Contents" means to delete the contents of the cell, while "clears the text" means to delete text but leave numbers, dates, formulae, etc.

Since both of these can be accomplished via VBA, you need to be specific as to what your want to clear.

There are also options for the type of button you want to use:
- A shape on a worksheet
- A button on a tool bar
- A button on a form
- A custom menu item

Depending on the version of Excel you are using, how you create these buttons may differ. Everything below is for 2003 and back. The same features are available in 2007, but they may be in different places.

For a shape on a worksheet, simply create any shape from the Drawing toolbar, right click it and choose "Assign macro". Scroll down the list to the macro your want and choose it. When you hover over the shape your cursor should turn into a finger.

For a toolbar button or a menu item, use Tools...Customize..Commands tab and scroll down to Macros.

Drag one of the 2 items in the right hand pane to a toolbar or Menu and drop it there. With the Customize window still open, right the item and choose "Assign macro". From the same right-click list you can change the picture, text, etc. on the button.

As far as the code itself, the simplest is if you want to clear the contents - everything - from the cell:

Sub ClearContentsOfRange()
End Sub

If you want to clear only the text, you have to loop through each cell to check if it is text before you clear it:

Sub ClearTextFromRange()
 For Each myCell In Range("myDelete")
  If Application.WorksheetFunction.IsText(myCell) _
    Then myCell.ClearContents
End Sub

One final note:

You will notice that I did not use "Delete" as my range name. I used myDelete. It is not good practice to name ranges or variables using the name of an Excel or VBA function. (There is a VBA method called Delete). Problems can range from confusion while reading the code all the way to illegal operation errors.

Report •
Related Solutions

Ask Question