Clear contents of an Excel worksheet with a command button

I've placed a command button on my Excel worksheet.

I want to be able to clear the contents (set them to zero) of cells C3 through C10, D3 through D10 and E3 through E10 by clicking on the button.

Each of the cells is formatted as "Accounting".

However, I want to give the user the chance to opt out of clearing the contents via a VBYesNo message.

Something like "Are you sure you want to clear the contents of this worksheet?"

If the user selects Yes, I not only want the cells' contents to be cleared but for the worksheet to be saved as well.

Thank you.
Who is Participating?
Martin LissConnect With a Mentor Older than dirtCommented:
I forgot about saving the sheet.
Martin LissOlder than dirtCommented:
Try this.
NorieConnect With a Mentor VBA ExpertCommented:
In the attached workbook I've created 2 buttons, one from the Forms toolbar and one from the ActiveX toolbar.

They both call the below code to clear the range on their respective sheets.
Sub ClearCells(ws As Worksheet)
Dim Ans As VbMsgBoxResult

    Ans = MsgBox("Are you sure you want to clear the contents" & vbCrLf & "of worksheet " & ws.Name & " ?", vbYesNo + vbQuestion, "Clear cells")
    If Ans = vbYes Then
        ws.Range("C3:E10").Value = 0
    End If

End Sub

Open in new window

Martin LissOlder than dirtCommented:
Here is a project where a "msgbox" is created from a form that I wrote years ago. One problem you'll face is finding a vbExlamation icon or picture file.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.