Macros to delete check boxes

[Guest]

I need to find a way to delete the check mark within a checkbox button so the
customer can clear chcked items. I tried a macro but it will not uncheck the
box. Do you have a solution. Thanks

 

[Nick Hodge]

Gary

Depends if you are using checkboxes from the forms tool box or the control
one (ActiveX).

This code will do both. The first line clears all the forms checkboxes, the
for each...next loop goes through the collection setting all the values to
false (un-checked)

Sub Uncheckboxes()
Dim wks As Worksheet
Dim ckbx As OLEObject
Set wks = Worksheets("Sheet1")
With wks
.checkboxes.Value = False 'Form type clear
For Each ckbx In wks.OLEObjects
ckbx.Object.Value = False 'ActiveX type clear
Next ckbx
End With
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Dave Peterson]

What kind of checkbox and where are they located.

I'm guessing on a worksheet.

If from the Forms toolbar:

Option Explicit
Sub testme01()
ActiveSheet.CheckBoxes.Value = xlOff
End Sub

If from the control toolbox toolbar:

Option Explicit
Sub testme02()
Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is msforms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub

 

[Guest]

I appreciate your assistance. I will try this ASAP. Do I set up a separate
macro to clear the check boxes or can I add this code to the existing clear
button that deletes everything else on the sheet.

 

[Guest]

I have a number of check boxes from the control toolbar that allow a customer
to check of if a function like folding is needed. I want to clear the
informaion using a macro so the customer doesn't have to manually uncheck
each box when ever a new job is created. There is other information on the
sheet that I can clear using a macro. Where can I put the code to also clear
any checked items along with the other information. Can I add it to the
existing macro.
Thanks for your help!

 

[Guest]

I have a number of check boxes from the control toolbar that allow a customer
to check of if a function like folding is needed. I want to clear the
informaion using a macro so the customer doesn't have to manually uncheck
each box when ever a new job is created. There is other information on the
sheet that I can clear using a macro. Where can I put the code to also clear
any checked items along with the other information. Can I add it to the
existing macro.
Thanks for your help!

 

[Guest]

Worked perfectly. I added the code to the established Clear macro and it
removed all the checks in the work sheet. Thank you for all your help.

 

[Guest]

Worked perfectly. I added the code to the established Clear macro and it
removed all the checks in the work sheet. Thank you for all your help.

 

[Guest]

No matter how/where I paste this can I get it to work. I have my clear button
working perfectly to delete input information into specific cells, but I can
not seem to paste this exactly right to get it to work for my checkmark boxes.

Worked perfectly. I added the code to the established Clear macro and it
removed all the checks in the work sheet. Thank you for all your help.

 

[Bob Phillips]

Are you sure that you have checkboxes form the control toolbox, not from the
Forms toolbar?

 

[Guest]

The checkboxes are from the Forms toolbar and I am trying to paste in the
code below for "If from the Forms toolbar:" but, if it really works, I still
can not seem to paste it to the right place..........

 

[Bob Phillips]

Mel,

Step by step.

Go into the VB IDE (Alt-F11)
Insert a new code module (menu Insert>Module)
Copy Dave's code

Now go back to your worksheet and select you button.
Right-clock the button
Select the Assign Macro option
Pick that macro.

Fingers crossed, all will be well.

--
HTH

Bob Phillips

The checkboxes are from the Forms toolbar and I am trying to paste in the
code below for "If from the Forms toolbar:" but, if it really works, I still
can not seem to paste it to the right place..........

 

[Guest]

Bob,

Worked perfect, thank you!!

I now have a button/macro to delete information in specific cells and a
button/macro to clear the check boxes and both were created from the Forms
toolbar.
However............ I have now been trying (unsuccessfully again) to paste
the script from either of the macros into it's counterpart to clear both the
cells and checkboxes using only one button.
I have tried pasting back and forth in every way I thought it might possibly
work but regardless of how I put it in and attempt to run the macro I get a
variety of errors.

I think I already owe you lunch or something, but can you point me in the
right direction again?

Thanks! Mel

 

[Guest]

Ok, this is either going to kill me or make me stronger..........

I have successfully combine my "clear" functions into one button now
(clearing text/numbers from cells and clearing checks from
boxes).................

NOW....... NOW.......... my problem is that when I protect the sheet........
the "clear" button that worked perfectly gives a "Run-time error'1004',
Unable to set the Value property of the CheckBoxes class".

If I turn protection back off, the button works perfectly.

I have to be able to protect the sheet so users can only check boxes and
enter text into specific cells..........

Ideas/suggestions??

Thank you!!

Bob,

Worked perfect, thank you!!

I now have a button/macro to delete information in specific cells and a
button/macro to clear the check boxes and both were created from the Forms
toolbar.
However............ I have now been trying (unsuccessfully again) to paste
the script from either of the macros into it's counterpart to clear both the
cells and checkboxes using only one button.
I have tried pasting back and forth in every way I thought it might possibly
work but regardless of how I put it in and attempt to run the macro I get a
variety of errors.

I think I already owe you lunch or something, but can you point me in the
right direction again?

Thanks! Mel

 

[Dave Peterson]

You can loop through both types of checkboxes with something like:

Option Explicit
Sub testme01()

Dim CBX As CheckBox
Dim OLEObj As OLEObject

For Each CBX In ActiveSheet.CheckBoxes
CBX.Value = xlOff
Next CBX

For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub


Alternatively, you could unprotect the worksheet, do your stuff, and then
reprotect the worksheet.

 

[Bob Phillips]

Mel,

Is this what you want?

Sub ClearCheckboxes()
Dim oCB As CheckBox
Dim fProtect As Boolean
With ActiveSheet
fProtect = .ProtectContents
.Unprotect
For Each oCB In .CheckBoxes
oCB.Value = xlOff
Range(oCB.LinkedCell).Value = ""
Next oCB
If fProtect Then .Protect
End With
End Sub

 

[Dave Peterson]

I didn't even have to unprotect the worksheet if I looped through those
checkboxes (from the Forms toolbar).

Doing all at once did require the unprotection, though.

 

[Guest]

Dave & Bob,

Thank you both for all of your help. I have used some of what both of you
have sent to me and it helped me to dig into some of my format settings for
the boxes and come up with the solution. Everything on the sheet is working
as needed.

Thanks again!!

Mel Ables

 

[Bob Phillips]

I had to unprotect Dave to clear the linked cell.

Bob

 

[Dave Peterson]

I would think that there wouldn't be a linked cell--or that those linked cells
would already be unlocked.

If the the linked cells were locked and the worksheet protected, then the
checkbox and the linked cell get confused (true in the cell with no checkmark
could happen or false in the cell and the checkmark showing).

I had to unprotect Dave to clear the linked cell.

Bob