Tick Cell Upon Selection

Got any Excel/VBA Questions? Free Excel Help

It is not too uncommon for an Excel Workbook to be used to collect data for such things as surveys etc. Perhaps the most common way this is done, is to offer the user x amount of answer choices and place a Checkbox along-side each choice. The user then checks the appropriate checkboxes. The problem with using this method is that your Workbook can soon end up with hundreds of checkboxes. This can not only make a Workbook unstable but can also greatly increase file size!

Let's look at just how easily we can use some very simple VBA code to have any cell, within a specified range, ticked upon selecting it. If the cell within the specified range, already has a tick, the code will remove it. The trick to the code is the use of the letter "a" in a cell that has had it's font formatted to marlett. When the time comes to add up the results, it is simply a matter of using the COUNTIF Function to count the occurrences of the letter "a". For example

=COUNTIF($A$1:A$100,"a")

The code examples below work only on range A1:A100, but can be easily modified to suit any range. To use this code, activate the Worksheet the ticks should be displayed on, right click on the sheet name tab and select "View Code". Now paste in either CODE 1 (to have the cell ticked by selecting it) or CODE 2 (to have the cell ticked by double clicking it)

'CODE 1

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 = "Marlett"

                If Target = vbNullString Then

                    Target = "a"

                Else

                    Target = vbNullString

                End If

        End If

End Sub

'CODE 2

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 = "Marlett"

                If Target = vbNullString Then

                    Target = "a"

                Else

                    Target = vbNullString

                End If

    End If

End Sub

Once the desired code is in place, simply click the top right X to get back to Excel, save and you are ready to go.