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

[kimberlie]

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

 

[dmt32]

Hi welcome to the board.

See if these codes will help you.

To clear the Form Controls try:

 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:

 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

 

[kimberlie]

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