Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Would there be a reason why the code below will not uncheck a Checkbox
that is "True" or ticked? Sub UncheckBoxes() Dim cb As CheckBox Application.ScreenUpdating = False Sheets("Input").Select For Each cb In ActiveSheet.CheckBoxes cb.Value = False Next cb End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
It works if the checkboxes are from the Forms toolbar, but not Control
Toolbox checkboxes. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ... Would there be a reason why the code below will not uncheck a Checkbox that is "True" or ticked? Sub UncheckBoxes() Dim cb As CheckBox Application.ScreenUpdating = False Sheets("Input").Select For Each cb In ActiveSheet.CheckBoxes cb.Value = False Next cb End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Did you assign a linked cell to those checkboxes?
Did you lock those linked cells? Did you protect the worksheet that contained those linked cells? ps. You could drop the .select and just use: For Each cb In sheets("Input").CheckBoxes Sean wrote: Would there be a reason why the code below will not uncheck a Checkbox that is "True" or ticked? Sub UncheckBoxes() Dim cb As CheckBox Application.ScreenUpdating = False Sheets("Input").Select For Each cb In ActiveSheet.CheckBoxes cb.Value = False Next cb End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Thanks Dave, took what Bob said and sourced this piece of code that
runs Sub DelAllCheckBoxes() Dim shp As Shape Application.ScreenUpdating = False Sheets("Sheet1").Activate On Error Resume Next For Each shp In ActiveSheet.Shapes If shp.FormControlType = xlCheckBox Then shp.Delete End If Next shp On Error GoTo 0 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
That doesn't look like you're unchecking checkboxes.
But glad you have what you want. Sean wrote: Thanks Dave, took what Bob said and sourced this piece of code that runs Sub DelAllCheckBoxes() Dim shp As Shape Application.ScreenUpdating = False Sheets("Sheet1").Activate On Error Resume Next For Each shp In ActiveSheet.Shapes If shp.FormControlType = xlCheckBox Then shp.Delete End If Next shp On Error GoTo 0 End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Now you have me worried Dave, it does uncheck what I have, but maybe
there are instances when it won't work. I have taken my CheckBox from the control toolbar |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Your totally correct Dave, I copied the wrong code to the NG, this is
what I should have shown Doh! Sub UncheckBoxes() Application.ScreenUpdating = False Sheets("Input").Activate For Each ctrl In ActiveSheet.OLEObjects If UCase(TypeName(ctrl.Object)) = "CHECKBOX" Then If ctrl.Object.Value = True Then ctrl.Object.Value = False End If End If Next End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
That looks better <vbg.
Here's another way: Option Explicit Sub Uncheckboxes2() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("Input") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Sean wrote: Your totally correct Dave, I copied the wrong code to the NG, this is what I should have shown Doh! Sub UncheckBoxes() Application.ScreenUpdating = False Sheets("Input").Activate For Each ctrl In ActiveSheet.OLEObjects If UCase(TypeName(ctrl.Object)) = "CHECKBOX" Then If ctrl.Object.Value = True Then ctrl.Object.Value = False End If End If Next End Sub -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Dave whats the different effects between both codes?
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Uncheck every Check Box Q
Nothing in the effect.
Some difference in the functions used. Sean wrote: Dave whats the different effects between both codes? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check or uncheck a check box based on a cell value | Excel Discussion (Misc queries) | |||
Uncheck Check Boxes | Excel Programming | |||
check/uncheck a checkbox | Excel Programming | |||
How do I check/uncheck ten or odd Checkboxes by click on one check | Excel Discussion (Misc queries) | |||
Check / Uncheck Box | Setting up and Configuration of Excel |