vba code button to clear all; checkboxes, comboboxes, cells, counts, etc...


New Member
Feb 18, 2016
Hi all,

I am new to vba and i have tried to find a code on here for clearing my whole worksheet but i cant seem to find one which does the specifics i want to or does all the bits :) if i missed something id be very grateful for a thread link...

Trying to create a new prf for my team, and i have a mixture of; normal cells to fill in which reference to other worksheets, over a hundred form checkboxes, some forms to fill in if checkbox is true, comboboxes, one which looks up a different set list if a checkbox is true.

Now i want to have a button to clear this worksheet, so make all my checkboxes false, make my dropdowns go blank, and clear the cells which have to be manually typed in (which will hopefully clear the other sheets that reference data from this main worksheet, if my code is correct ;) ) also i have a runnign total but i guess if the dropdowns go back to blank that will go back to 0?

Can anyone help with the code? all my checkboxes, comboboxes and arrays are separately named so i didn't want to list out every single one, but i also just dont want to clear the whole sheet (i need the formatting/headers/etc to stay) also i would be so grateful if you do help with the code if you could 'note me the parts so i can see which part of code does what ;)

Thank you in advance :)

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi welcome to the board.

See if these codes will help you.

To clear the Form Controls try:

Rich (BB code):
 Sub ClearFormControls()

    Dim xshape As Shape
    For Each xshape In Worksheets("Sheet1").Shapes
        If xshape.Type = msoFormControl Then xshape.ControlFormat.Value = msoFalse

End Sub

To Clear the Cells try:

Rich (BB code):
 Sub ClearRanges()   
    Dim rng As Range
    Set rng = Worksheets("Sheet1").Range("A10,B10:B20,C10,D10:D20")

    Application.EnableEvents = False

    'if sheet protected unprotect
    'add password as required
    rng.Parent.Unprotect Password:=""

    On Error Resume Next
    'clear input cells that contain constants
    Application.EnableEvents = True

    On Error GoTo 0

End Sub

This should clear cells where users input directly but ignore cells in that contain formulas.
In addition, example cell input ranges will also need to be adjusted to meet with your project need.

For both codes you will need to change the worksheet name where shown in RED as required.

Hope Helpful

Last edited:
Upvote 0
thank you for your help, if anyone wants to know i did it with multiple sub's but then ran them all on the button press ;)

Sub ResetCheckboxes()
'clears all checkboxes,it changes all ActiveX (which is what you've used) checkboxes to false unless the name ends in 'no'. If the name ends in 'no' then the process changes that to true. The on error code accounts for 'yes' checkboxes which have to be skipped or they'll cause an error.
For Each CheckBox In Worksheets("PRF").OLEObjects
On Error Resume Next
If (Right(CheckBox.Name, 2) = "no") Then
CheckBox.Object.Value = True
CheckBox.Object.Value = False
End If
On Error GoTo 0
End Sub
Sub Clearall()
' when post it ticked all manually entered boxes, clear contents
End Sub
Private Sub Cspersonupdate_Change()
ComboBox1.Value = "commercial"
ComboBox2.Value = "commercial"
End Sub
Sub postit()
End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back