Clear check box macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In an excel program, I have set up a command button that will automatically
clear out the information in certain cells. My problem is that within my
excell document I have some check boxes. I want to set my macro up so that
it will also look at my check boxes and if the box is checked, I want it to
uncheck it and if it is unchecked I want it to leave it alone. Can someone
help me??
 
Hi Kyla, If your CheckBox's are in a userform and also believe on a
worksheet. You can us this code to clear all the checkbox's.

enjoy, Rick , FBKS, AK


Dim ctrl as Control

With Me
For Each ctrl In .Controls
if TypeName(ctrl) ="CheckBox" then
ctrl.value = False
endif
next ctrl

End With
 
Probably the easiest way is to link the value of the checkbox to a cell and
then just change the value of the cell to false...

If your checkbox is from the forms toolbar then right click the box and
select Format Control and then on the Control tab Add a cell to link to.

If your checkbox is from the control toolbox put the sheet in design mode by
hitting the Design mode button on the toolbar. Then right click the check box
and select properties. Add a reference to the linked cell property.
 
Hi Rick.

Thank you for responding to my email. I am having a problem with the code
you gave me though. When I compile my VBAProject it give me this error:

Compile Error:
Method or Data Member not Found

Also, should I be subsistuting another word for TypeName? If so what should
go here?
 
Kyla, What type of Checkbox's are you using? Form Control or Control
toolbox. And whwere are your checkboxs localed at ?. I'll check with you
later, have go to work..

later Rick
 
Hey Rick,

My check boxes were chosen from the Control toolbox, and they are located in
an excel spreadsheet, not on a form. I need to know exactly how to
incorporate the clear check box code with my existing code that clears the
other fields. I created a button on my spreadsheet named Clear Kronos Page
and in View Code wrote the macro to clear out the identified info. Here is
my existing code:

Private Sub CommandButton1_Click()

' KronosClear Macro
' Macro recorded 4/26/2006 by kdockery
'
' Keyboard Shortcut: Ctrl+k
'
Range("A6:D18").Select
Selection.ClearContents
Range("E6:F18").Select
Selection.ClearContents
Range("G6:H18").Select
Selection.ClearContents
Range("J6:L18").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=7
Range("A23:I41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-20
Range("A6").Select
End Sub

I hope this helps!

Kyla Dockery
 
Hey Rick,

I finally kept messing around with my code until I figured out what I needed
to do. It all works beautifully now! Thank you so much for the help you
gave me earlier.

Kyla
 
I am having a similar issue. Could you send me a copy of your complete code
that deletes data and unchecks boxes?
 
Back
Top