Data input in Excel

  • Jul. 27, 2024
Example files with this article:
  • Checkboxes and converting data validation lists

    • Wow, time flies ! It has been a while since I posted Excel articles. Here is an article covering the new Excel checkbox controls and how to convert existing data validation lists into checkboxes.

    Introduction

    The long-awaited checkbox controls finally arrived in the continuously updating Excel versions. Beta testing is done and now the general public can start to make use of them. And I must say, they are great and easy to use! You can just select 1 or more cells and insert checkboxes who will be placed in the cells:

    For a great introduction, see this Youtube video or similar videos.

    Here's a picture from Jon Acampora of Excel Campus, where he applied conditional formatting to the checkboxes:

    You do not need VBA for this task (but you could - see below). The checkbox has 3 values: TRUE or FALSE, or the cell could be cleared with the Del key. For TRUE, you get a checkbox that is checked, for FALSE, you get a checkbox that is unchecked, and for Delete you get a ghosted checkbox (it's still there but less visible and a bit faded).

    When you first get them in your Excel application, you will probably play around and insert a bunch of them in empty cells. Then you continue exploring the possibilities:

    • Hit Spacebar to activate/deactivate a checkbox
    • Formulas (including conditional formatting and everything else) can work with the TRUE/FALSE values
    • You can copy/paste values a bunch of TRUE/FALSE values in cells with checkboxes and they will be switched
    • Or, if you already have the TRUE/FALSE values, just select the cells, insert checkboxes and the state remains.
    • In a ListObject they are extended when you add rows.
    • In pivot tables and slicers they will read as TRUE and FALSE.

    All in all, I think it's good stuff. I like them and they appear to be much cleaner than the awful ActiveX / Forms things or YES/NO validation lists (now checkboxes are independent of a language setting and lookup lists).

    After your initial tests you will soon start noticing areas in existing files where you will want to replace 2-option data validation lists with the new checkboxes. Typically, these short lists like True/False, Yes/No (in any particular language) are good candidates to be converted into visual checkboxes.

    VBA-code

    For a couple of them, that's not too much of a hassle. However, when you need to convert many of them, it's getting more tedious. At least to me. And when you can write VBA-code it becomes peanuts with code like the below. I share with you the full code. Grab the code, select the cells to be converted, run the macro once and you're done !

    As can be read in the commentary texts, what I support in my replacements towards the new checkboxes is pretty extensive:

    • Data validation lists with hardcoded Yes/No
    • Data validation lists with hardcoded True/False
    • Data validation towards a cells that are captured with a named range, if the lookup range contains 2 cells, a Yes and a No
    • Data validation towards a cells that are captured with a named range, if the lookup range contains 2 cells, a True and a False
    • Data validation towards a cells that are captured with a range address, if the lookup range contains 2 cells, a Yes and a No
    • Data validation towards a cells that are captured with a range address, if the lookup range contains 2 cells, a True and a False
    • In case the cell has data validation but a formula resides in the cell, then we ignore the cell unless the formula gives True or False
    All lookup strings are case insensitive, so you can convert Yes and yes and yES, for example. Next to English I also support Dutch validation lists:
    • Yes becomes Ja
    • No becomes Neen
    • True becomes Waar
    • False becomes Onwaar

    I do not convert ActiveX or Forms control checkboxes to these checkboxes, only data validation lists. The former are typically not used in abundance (at least, I don't) and I think that they can still have their good use cases.

    One final note. If you need to convert 100s of such lists, you might see a performance decrease as I do it cell per cell. By all means, feel free to optimize the code for performance. For instance, use the GoTo functionality in Excel and VBA to select all cells with the same validation as a given cell. Like that, you can do 100s or more in 1 action. When you are done, please notify me such that I can post your code on this page as well. 

    Sub Switch_Boolean_Data_Validation_Lists_To_Checkboxes()
    

    'Wim Gielis
    'July 2024
    'https://www.wimgielis.com


    'Instructions:
    'Select the desired cells to be converted and run the macro

    'Purpose:
    'Switch from Yes/No kind of Excel data validation lists to the Excel checkbox controls
    'The typical dropdown lists for Yes and No are converted to these checkboxes
    'Allowed validation lists are as follows:
    '* Yes,No (hardcoded) (following the list separator of the regional settings)
    '* No,Yes (hardcoded) (following the list separator of the regional settings)
    '* True,False (hardcoded) (following the list separator of the regional settings)
    '* False,True (hardcoded) (following the list separator of the regional settings)
    '* =LookupRangeWithCells (a range that contains 2 cells, a cell with yes and a cell with no, or a True and a False)
    '* =LookupRangeWithName (a named range that contains 2 cells, a cell with yes and a cell with no, or a True and a False)
    '* in all of the above rules, Dutch language support next to English. If needed, add other language constants yourself.

    'Limitations/notes:
    'Cells with formulas are not skipped but the formula is kept as such (not overwritten with a hardcoded True or False)
    'English and Dutch languages only
    '   yes, no, true, false - other languages will have other designations for true, false and obviously also for yes and no)
    '   English: yes, no, true, false
    '   Dutch  : ja, neen, waar, onwaar
    'We do not convert ActiveX or Forms control checkboxes to the checkboxes, only data validation lists
    'We go cell by cell, which will be slower if you have many checkboxes, then converting a whole range at once

    Dim cell As Range
    Dim rSelection As Range
    Dim ValidatedCells As Range
    Dim vCellValue As Variant
    Dim validationType As Long
    Dim validationFormula1 As String
    Dim lToReplace As Single
    Dim LookupRange As Range

    Set rSelection = Selection

    'Get the cells in the selection where data validation is applied
    On Error Resume Next
    Set ValidatedCells = rSelection.Cells(1).SpecialCells(xlCellTypeAllValidation)
    If Not ValidatedCells Is Nothing Then
        Set ValidatedCells = Application.Intersect(ValidatedCells, rSelection)
    End If
    On Error Resume Next

    If ValidatedCells Is Nothing Then
        MsgBox "The selected cells (" & rSelection.Address(0, 0) & ") do not contain validated cells. The macro stops.", vbExclamation
        Exit Sub
    End If

    Application.ScreenUpdating = False

    'Loop through each cell in the selected range
    For Each cell In ValidatedCells.Cells

        With cell

            lToReplace = 0
            validationType = .Validation.Type

            'Check if the cell has data validation of the List type
            If validationType = 3 Then '3 = xlValidateList

                'Get the formula or values of the data validation list
                validationFormula1 = .Validation.Formula1
                validationFormula1 = LCase(Replace(.Validation.Formula1, Application.International(xlListSeparator), ","))

                'Check if the list is hardcoded as "Yes,No" or variants thereof, or True/False with variants
                Select Case True
                Case (validationFormula1 = "true,false") Or (validationFormula1 = "false,true")
                    lToReplace = 1

                Case (validationFormula1 = "waar,onwaar") Or (validationFormula1 = "onwaar,waar")
                    lToReplace = 1

                Case (validationFormula1 = "yes,no") Or (validationFormula1 = "no,yes")
                    lToReplace = 2

                Case (validationFormula1 = "ja,neen") Or (validationFormula1 = "neen,ja")
                    lToReplace = 2

                Case Left(validationFormula1, 1) = "="

                    Set LookupRange = Nothing

                    'First, let's try a Named range
                    On Error Resume Next
                    Set LookupRange = ActiveWorkbook.Names(Mid(validationFormula1, 2)).RefersToRange
                    On Error GoTo 0

                    'No luck with the previous case. Let's try a range address instead.
                    If LookupRange Is Nothing Then
                        Set LookupRange = Range(Mid(validationFormula1, 2))
                    End If

                    If Not LookupRange Is Nothing Then
                        If WorksheetFunction.CountIf(LookupRange, True) + _
                           WorksheetFunction.CountIf(LookupRange, False) = LookupRange.CountLarge Then

                            lToReplace = 1

                        End If
                    End If

                    If Not LookupRange Is Nothing Then
                        If WorksheetFunction.CountIf(LookupRange, "yes") + _
                           WorksheetFunction.CountIf(LookupRange, "no") = LookupRange.CountLarge Then

                            lToReplace = 2

                        End If
                    End If

                    If Not LookupRange Is Nothing Then
                        If WorksheetFunction.CountIf(LookupRange, "ja") + _
                           WorksheetFunction.CountIf(LookupRange, "neen") = LookupRange.CountLarge Then

                            lToReplace = 2

                        End If
                    End If

                End Select

                'Let's now replace validation lists with checkboxes
                If lToReplace > 0 Then

                    'Add a new Excel checkbox
                    'Set the checkbox state as before when it was hardcoded (formulas are kept)
                    If Not .HasFormula Then

                        Select Case LCase(.Value2)
                        Case vbNullString, "yes", "no", "true", "false", "ja", "neen", "waar", "onwaar"
                            vCellValue = .Value2
                            .ClearContents

                            'Convert the validation list into a checkbox control
                            .Validation.Delete
                            .CellControl.SetCheckbox

                            Select Case LCase(vCellValue)
                            Case "yes", "true", "ja", "waar": .Value = True
                            Case "no", "false", "neen", "onwaar": .Value = False
                            Case vbNullString: .ClearContents
                                'The Excel checkboxes have a triple state:
                                'TRUE/FALSE: same as hitting the Spacebar on the keyboard
                                'Empty: same as hitting delete on the keyboard - the checkbox is ghosted but not really gone (difference with Google Sheets)
                            End Select
                        Case Else
                            'This case is possible. The list choices of a user are not necessarily validated
                            'Or, you could have had a formula in a cell and only afterwards data validation was applied to the cell
                            'Or, VBA could have set a value that is not part of the validation choices.
                            'We ignore this cell
                        End Select

                    ElseIf lToReplace = 1 Then

                        'Only for True/False we can allow formulas. For Yes/No, the checkbox is not set (Excel does not allow it)
                        'Since formulas are kept, this part is easier
                        .Validation.Delete
                        .CellControl.SetCheckbox

                    End If

                End If

            End If

        End With

    Next

    Application.ScreenUpdating = False

    MsgBox "Specified data validation lists have been removed from the selected cells, where appropriate.", vbInformation


    End Sub

    Enjoy and do let me know if and how you use my code ! Send me an email.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links