Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working With Checkboxes
Excel 2007
I am building a spreadsheet that is essentially a checklist. Currently, on each line item we just type an "x" if that item needs to be configured. What I would like to do is put checkboxes on each line item, and then add Check All and Clear All checkboxes at the top of the list. I did something like this a couple of years ago in Excel 2003, and I seem to remember that it required code for the Check All and Clear All checkboxes that would enumberate all the checkboxes in the list and then loop through and set each checkbox. I could probably set that up again, but the problem is that we don't know what will be added or removed from the list in the future, and I would like things setup in such a way that anyone modifying the spreadsheet in the future will not need to edit the programming. How can I setup this functionality so that the macro will work--without any editing--no matter what gets added or deleted in the future? --Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working With Checkboxes
Try something like this. Of course, this assumes that the check boxes aren't
renamed. Option Explicit Sub ClearCheckBox() Dim myWS As Excel.Worksheet Dim myShape As Excel.Shape Set myWS = ActiveSheet For Each myShape In myWS.Shapes Debug.Print myShape.Name, Debug.Print myShape.ControlFormat.Value If myShape.Name Like "Check*" Then myShape.ControlFormat.Value = -4146 End If Next myShape End Sub "Thomas M." wrote: Excel 2007 I am building a spreadsheet that is essentially a checklist. Currently, on each line item we just type an "x" if that item needs to be configured. What I would like to do is put checkboxes on each line item, and then add Check All and Clear All checkboxes at the top of the list. I did something like this a couple of years ago in Excel 2003, and I seem to remember that it required code for the Check All and Clear All checkboxes that would enumberate all the checkboxes in the list and then loop through and set each checkbox. I could probably set that up again, but the problem is that we don't know what will be added or removed from the list in the future, and I would like things setup in such a way that anyone modifying the spreadsheet in the future will not need to edit the programming. How can I setup this functionality so that the macro will work--without any editing--no matter what gets added or deleted in the future? --Tom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working With Checkboxes
Why would you want to add all those extra controls for? You can stay with
your current X system and use the following macros (which you can assign to Buttons) to clear and check the column all at once. You didn't tells us anything about your worksheet's structure, so I guessed that Column A is where you put the X's and Column B contains the descriptions for what is being checked and unchecked. Sub CheckAll() Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row).Value = "X" End Sub Sub ClearAll() Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row).Value = "" End Sub If you want to simplify the process of checking and unchecking the Column A cells, you can use this event code to make a double-click toggle the X's on and off... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim LastRow As Long LastRow = Cells(Rows.Count, "B").End(xlUp).Row If Target.Column = 1 And Target.Row <= LastRow Then Cancel = True If Target.Value = "" Then Target.Value = "X" Else Target.Value = "" End If End If End Sub To install this event code (the two macros should go in Modules), right-click the tab at the bottom of the sheet that you want to have this functionality, select View Code from the popup menu that appears and then Copy/Paste the event procedure into the code window that appeared. Now, when you double click a cell in Column A that is on a row equal to or less than the last row of data in Column B, the cell will toggle between an X and no X. -- Rick (MVP - Excel) "Thomas M." wrote in message ... Excel 2007 I am building a spreadsheet that is essentially a checklist. Currently, on each line item we just type an "x" if that item needs to be configured. What I would like to do is put checkboxes on each line item, and then add Check All and Clear All checkboxes at the top of the list. I did something like this a couple of years ago in Excel 2003, and I seem to remember that it required code for the Check All and Clear All checkboxes that would enumberate all the checkboxes in the list and then loop through and set each checkbox. I could probably set that up again, but the problem is that we don't know what will be added or removed from the list in the future, and I would like things setup in such a way that anyone modifying the spreadsheet in the future will not need to edit the programming. How can I setup this functionality so that the macro will work--without any editing--no matter what gets added or deleted in the future? --Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Checkboxes | Excel Programming | |||
Checkboxes | Excel Programming | |||
Quick Start to working with checkboxes etc within a worksheet | Excel Programming | |||
Help with checkboxes | Excel Programming | |||
checkboxes | Excel Programming |