Formulation of the problem
We have a list of something on the sheet. I would like to use the usual checkboxes or bold dots to mark the selected elements. More or less like this:
Solution
Right-click on the sheet tab where the list is located and select from the context menu Source text (Source Code). You should be in the Visual Basic Editor.
Copy this code there:
'Check if cell was single clicked Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A100")) Is Nothing Then Application. EnableEvents = False Target.Font.Name = "Marlett" Target = "a" Application.EnableEvents = True End If End Sub 'Uncheck if cell was double-clicked Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("A2:A100")) Is Nothing Then Application.EnableEvents = False Cancel = True Target.ClearContents Application.EnableEvents = True End If End Sub The first half of the code checks to see if solitary clicking on cells in the range A2:A100 and if it was, then puts a “tick” – a sign that is in the font Marlett located on the letter “a”. The second half of this code unchecks the box when double click on a cell. If necessary, replace “A2:A100” in this code with your range.
If you need to count the number of marked elements, you can always use a simple formula that counts the number of cells with the letter “a” in our range:
=SCHÖTESLI(A2:A100;”a”)
=COUNTIF(A2:A100;»a»)
If it is necessary that the user can mark only one element from the list, then the code is simplified – just one macro is enough:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A100")) Is Nothing Then Application.EnableEvents = False Range("A2:A100").ClearContents Target.Font.Name = "Marlett" Target = "h" Application.EnableEvents = True End If End Sub This macro first completely clears our column A, and then enters the letter “h” in the current cell, which in the Marlett font will give us the characteristic one-of-choice character – a bold point. Thus, it will not work to put two such symbols – only the choice of one element from the list will be available. To retrieve the selected element, you can use the standard function VPR (VLOOKUP), which will look for the dot character (i.e. the letter “h”) in the first column of our table and, when found, give out the last name from the second:
- Bulleted and numbered lists in Excel as in Word
- What is a macro? Where to insert macro code in VBA? How to use them?
- What is the VLOOKUP function and how to use it for value substitution