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

kimberlie

New Member
Joined
Feb 18, 2016
Messages
4
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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
    Next


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
    rng.Cells.SpecialCells(xlCellTypeConstants).ClearContents
    
    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

Dave
 
Last edited:

kimberlie

New Member
Joined
Feb 18, 2016
Messages
4
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
Else
CheckBox.Object.Value = False
End If
On Error GoTo 0
Next
End Sub
Sub Clearall()
' when post it ticked all manually entered boxes, clear contents
Sheets("PRF").Range("C3:C12").ClearContents
Sheets("PRF").Range("C39:C41").ClearContents
Sheets("PRF").Range("C17:C20").ClearContents
End Sub
Private Sub Cspersonupdate_Change()
ComboBox1.Value = "commercial"
ComboBox2.Value = "commercial"
End Sub
Sub postit()
Clearall
ResetCheckboxes
Cspersonupdate_Change
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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
Top