| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I check/uncheck ten or odd Checkboxes by click on one check
Is it posible to use command button to check/uncheck all the Checkboxes in
the spread sheet? Please help, thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I check/uncheck ten or odd Checkboxes by click on one check
Hello Ken, Here are the macros to accomplish your tasks. Add a VBA Module to your project and copy this code into it. You can assign "ClearCheckboxes" to one command button and "CheckCheckboxes" to another. MACRO CODE: _____________________________ Sub ClearCheckBoxes() Dim Shp For Each Shp In ActiveSheet.Shapes X = Shp.Type If X = msoFormControl Then If Shp.FormControlType = xlCheckBox Then Shp.ControlFormat.Value = False End If End If Next Shp End Sub Sub CheckCheckBoxes() Dim Shp For Each Shp In ActiveSheet.Shapes X = Shp.Type If X = msoFormControl Then If Shp.FormControlType = xlCheckBox Then Shp.ControlFormat.Value = True End If End If Next Shp 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=497742 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I check/uncheck ten or odd Checkboxes by click on one c
Hi Leith,
Thanks for your help but I still can't get it working. What i did was created numerous of checkboxes and then a command button trhough "Control Toolbar". Than I click view codes and entered the codes that you had provided. It didn't do anything. Am i doing something wrong? Please help. Thanks! "Leith Ross" wrote: Hello Ken, Here are the macros to accomplish your tasks. Add a VBA Module to your project and copy this code into it. You can assign "ClearCheckboxes" to one command button and "CheckCheckboxes" to another. MACRO CODE: _____________________________ Sub ClearCheckBoxes() Dim Shp For Each Shp In ActiveSheet.Shapes X = Shp.Type If X = msoFormControl Then If Shp.FormControlType = xlCheckBox Then Shp.ControlFormat.Value = False End If End If Next Shp End Sub Sub CheckCheckBoxes() Dim Shp For Each Shp In ActiveSheet.Shapes X = Shp.Type If X = msoFormControl Then If Shp.FormControlType = xlCheckBox Then Shp.ControlFormat.Value = True End If End If Next Shp 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=497742 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I check/uncheck ten or odd Checkboxes by click on one c
This is the code I have for clearing Checkboxes:
__________________________________________________ __________________ 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 __________________________________________________ ___________________ But how do I do the opposite? What's the code for it? Please help. Thanks! "Ken Vo" wrote: Hi Leith, Thanks for your help but I still can't get it working. What i did was created numerous of checkboxes and then a command button trhough "Control Toolbar". Than I click view codes and entered the codes that you had provided. It didn't do anything. Am i doing something wrong? Please help. Thanks! "Leith Ross" wrote: Hello Ken, Here are the macros to accomplish your tasks. Add a VBA Module to your project and copy this code into it. You can assign "ClearCheckboxes" to one command button and "CheckCheckboxes" to another. MACRO CODE: _____________________________ Sub ClearCheckBoxes() Dim Shp For Each Shp In ActiveSheet.Shapes X = Shp.Type If X = msoFormControl Then If Shp.FormControlType = xlCheckBox Then Shp.ControlFormat.Value = False End If End If Next Shp End Sub Sub CheckCheckBoxes() Dim Shp For Each Shp In ActiveSheet.Shapes X = Shp.Type If X = msoFormControl Then If Shp.FormControlType = xlCheckBox Then Shp.ControlFormat.Value = True End If End If Next Shp 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=497742 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I check/uncheck ten or odd Checkboxes by click on one check
Hello Ken, This is a good example of the devil being in the details. There are 2 ways to create controls on a worksheet. One is by using the FORMS toolbar and the other is the CONTROL TOOLBOX. I had thought about including examples using both types since the are very different at the code level. The code I wrote was for the FORMS type which people use most often with worksheets. The CONTROL TOOLBOX is generally used on VBA UserForms. The behavior of either type of CheckBox is the same. If it is checked it's value property is TRUE and if it clear then the value property is FALSE. To check the checkbox... If .OLEObjects(I).progID = ChkBoxId Then .OLEObjects(I).Object.Value = TRUE End If 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=497742 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I check/uncheck ten or odd Checkboxes by click on one c
Thanks Leigh!
I appreciate your effort and time. It worked perfectly. "Leith Ross" wrote: Hello Ken, This is a good example of the devil being in the details. There are 2 ways to create controls on a worksheet. One is by using the FORMS toolbar and the other is the CONTROL TOOLBOX. I had thought about including examples using both types since the are very different at the code level. The code I wrote was for the FORMS type which people use most often with worksheets. The CONTROL TOOLBOX is generally used on VBA UserForms. The behavior of either type of CheckBox is the same. If it is checked it's value property is TRUE and if it clear then the value property is FALSE. To check the checkbox... If .OLEObjects(I).progID = ChkBoxId Then .OLEObjects(I).Object.Value = TRUE End If 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=497742 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
| check boxes - copy | Excel Discussion (Misc queries) | |||
| Excell Check Digit Formula | Excel Worksheet Functions | |||
| check box, so when you click on it it inserts a check mark into t. | Excel Discussion (Misc queries) | |||
| How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
Linear Mode
