Tick Cell Upon Selection
Category: [Excel] Demo Available
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.
See also:
Free Training Course: Lesson 1 - Excel Fundamentals
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.