Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from toolbox
Hi All
I have a worksheet that contains a number of checkboxes (all from the Control toolbox rather than the forms toolbar. I wish to have a button on the sheet which, when clicked, 'resets' all of the checkboxes to unchecked. Is there a quick and easy way of doing this? Many thanks for your help on this. Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from toolbox
Hello Andy, This will clear all the checkboxes it finds on the active worksheet, regardless of their names. Copy this code and placce it in a VBA module. Code: -------------------- Sub ClearAllCheckBoxes() Dim ChkBoxId As String ChkBoxId = "Forms.CheckBox.1" With ActiveSheet For I = 1 To .OLEObjects.Count If .OLEObjects(I).ProgId = ChkBoxId Then .OLEObjects(I).Object.Value = False End If Next I End With End Sub -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=487459 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from toolbox
Thank you Leith. Works perfectly. Just to clarify and hopefully learn something in the process. So the word OLEobjects refers to all control toolbox buttons, checkboxes etc on the sheet. So the code counts all the OLEObjects on the sheet. Using a for loop It then checks each one to ensure it is a checkbox and then assigns the value FALSE (unchecked). Thanks again Andy Leith Ross wrote: Hello Andy, This will clear all the checkboxes it finds on the active worksheet, regardless of their names. Copy this code and placce it in a VBA module. Code: -------------------- Sub ClearAllCheckBoxes() Dim ChkBoxId As String ChkBoxId = "Forms.CheckBox.1" With ActiveSheet For I = 1 To .OLEObjects.Count If .OLEObjects(I).ProgId = ChkBoxId Then .OLEObjects(I).Object.Value = False End If Next I End With End Sub -------------------- Sincerely, Leith Ross |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from toolbox
Hello Andy, You are correct. The Control Toolbox controls are embedded objects o the Worksheet, that's why they are referenced using OLEObject. Fo those who don't know, OLE stands for Object Linking and Embedding. The Object property allows you to access the properties of the embedde object, in this case the value property of the checkbox. The valu property when true displays the checkmark, and removes it when false. Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48745 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from toolbox
And there are other ways to check.
Instead of: If .OLEObjects(I).ProgId = ChkBoxId Then I'd use: If typeof .OLEObjects(I).object is msforms.checkbox then I find it a bit more intuitive. (but it's pretty much dealer's choice.) Andy wrote: Thank you Leith. Works perfectly. Just to clarify and hopefully learn something in the process. So the word OLEobjects refers to all control toolbox buttons, checkboxes etc on the sheet. So the code counts all the OLEObjects on the sheet. Using a for loop It then checks each one to ensure it is a checkbox and then assigns the value FALSE (unchecked). Thanks again Andy Leith Ross wrote: Hello Andy, This will clear all the checkboxes it finds on the active worksheet, regardless of their names. Copy this code and placce it in a VBA module. Code: -------------------- Sub ClearAllCheckBoxes() Dim ChkBoxId As String ChkBoxId = "Forms.CheckBox.1" With ActiveSheet For I = 1 To .OLEObjects.Count If .OLEObjects(I).ProgId = ChkBoxId Then .OLEObjects(I).Object.Value = False End If Next I End With End Sub -------------------- Sincerely, Leith Ross -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from toolbox
Thanks Leith and Dave This sort of feedback, from both of you, I find very useful as it gives me a chance to experiment and learn new ways of doing things, which invariably leads to neater and less code. Cheers Andy Dave Peterson wrote: And there are other ways to check. Instead of: If .OLEObjects(I).ProgId = ChkBoxId Then I'd use: If typeof .OLEObjects(I).object is msforms.checkbox then I find it a bit more intuitive. (but it's pretty much dealer's choice.) Andy wrote: Thank you Leith. Works perfectly. Just to clarify and hopefully learn something in the process. So the word OLEobjects refers to all control toolbox buttons, checkboxes etc on the sheet. So the code counts all the OLEObjects on the sheet. Using a for loop It then checks each one to ensure it is a checkbox and then assigns the value FALSE (unchecked). Thanks again Andy Leith Ross wrote: Hello Andy, This will clear all the checkboxes it finds on the active worksheet, regardless of their names. Copy this code and placce it in a VBA module. Code: -------------------- Sub ClearAllCheckBoxes() Dim ChkBoxId As String ChkBoxId = "Forms.CheckBox.1" With ActiveSheet For I = 1 To .OLEObjects.Count If .OLEObjects(I).ProgId = ChkBoxId Then .OLEObjects(I).Object.Value = False End If Next I End With End Sub -------------------- Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
Where is the control toolbox? | New Users to Excel | |||
Where is the control toolbox? | New Users to Excel | |||
HOW DO I GIVE A CONTROL TOOLBOX CHECKBOX A VALUE IN EXCEL? | Excel Worksheet Functions | |||
Controls Toolbox control vs Form Toolbox control | Excel Programming |