Clear check box macro

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

"Kyla Dockery" <[email protected]> wrote in message
news:[email protected]...
> 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??

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.
--
HTH...

Jim Thomlinson


"Kyla Dockery" wrote:

> 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 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?

"Rick Hansen" wrote:

> 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
>
> "Kyla Dockery" <[email protected]> wrote in message
> news:[email protected]...
> > 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??
>
>
>

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



"Kyla Dockery" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> "Rick Hansen" wrote:
>
> > 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
> >
> > "Kyla Dockery" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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??
> >
> >
> >

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

"Rick Hansen" wrote:

> 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
>
>
>
> "Kyla Dockery" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> > "Rick Hansen" wrote:
> >
> > > 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
> > >
> > > "Kyla Dockery" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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??
> > >
> > >
> > >
>
>
>

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

"Kyla Dockery" wrote:

> 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??

Hey Kyla, Your very welcome. I'm glad I could help..

enjoy Rick (Fbks, Ak)



"Kyla Dockery" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Kyla Dockery" wrote:
>
> > 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??