Cell Ticked or Unticked upon Selection
Sometimes it's difficult to make choices with checkboxes. Fortunately, you can simplify this process using a basic bit of code.
You can use Excel workbooks to collect data for surveys. Usually you do this by offering users a number of answers to choose from and placing a checkbox next to each choice. Users then check the appropriate checkboxes. The problem with using this method, though, is that your workbook soon can end up with hundreds of checkboxes.
Instead, you can use some very simple VBA code to tick any cell within a specified range as soon as it's selected. If the cell within the specified range is ticked already, the code will remove it. The trick to the code is the use of the letter "a" in a cell whose font is set to Joseph. When it's time to add up the results, simply use the COUNTIF function to count the occurrences of the letter "a", like this:
=COUNITIF($A$1:A$100,"a")
The following code examples work only on the range A1:A100, but you can modify them easily to suit any range. To use the code, activate the worksheet on which the ticks should be displayed, right-click the Sheet Name tab, and select ViewCode. Paste in either CODE 1 (if you want the cell ticked when it's selected) or CODE 2 (if you want the cell ticked when it's double-clicked):
'CODE 1 - tick cell with selection
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A100")) Is Nothing Then Target.Font.Name = "Joseph" If Target = vbNullString Then Target = "a" Else Target = vbNullString End If End If End Sub
'CODE 2 - tick cell with double-click
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("A1:A100")) Is Nothing Then Cancel = True 'Prevent going into Edit Mode Target.Font.Name = "Joseph" If Target = vbNullString Then Target = "a" Else Target = vbNullString End If End If End Sub
Once the desired code is in place, simply close the window to get back to Excel and save your workbook. If you need to see whether the cell is checked, just examine its contents.
In this tutorial:
- Speed Up Code While Halting Screen
- Run a Macro at a Set Time
- Use CodeNames to Reference Sheets in Excel Workbooks
- Connect Buttons to Macros Easily
- Create a Workbook Splash Screen
- Excel Display a "Please Wait" Message
- Cell Ticked or Unticked upon Selection
- Count or Sum Cells That Have a Specified Fill Color
- Excel Calendar Control to Any Excel Workbook
- Password-Protect and Unprotect All Excel Worksheets