It also has buttons to Clear all the selections, and select all the items.The premium version works with dependent lists too, and runs on a protected worksheet.1 Then Go To exit Handler On Error Resume Next Set rng DV = Cells. Enable Events = True End Sub If the worksheet is protected, the Cells. Type property of the target cell, to see if it contains a drop down list. In Excel 2010 and later, type in the filter Search box, just above the list of filter options.Special Cells(xl Cell Type All Validation) On Error Go To exit Handler If rng DV Is Nothing Then Go To exit Handler If Intersect(Target, rng DV) Is Nothing Then 'do nothing Else Application. Special Cells(xl Cell Type All Validation) code will not work. This example is on the Same Cell Protect sheet in the sample file. NOTE: The Premium Version also works on protected worksheets. The list is automatically filtered as you type, to show only the items that contain the text that has been entered in the Search box..Instead of limiting a data validation drop down list to a single selection, you can use this macro to allow multiple selections.Video and written instructions, and get the free workbook to see how it works.
I find these combinations validations in the "at selection screen" more tedious.For example, suppose you wanted to validate a credit card.All credit cards are 16 digits and the 16th digit can be calculated based on the first 15 numbers.Private Sub Worksheet_Change(By Val Target As Range) ' Developed by Contextures Inc. In Excel 2007, where there is no Search box, use the Text Filters command.' runs on protected sheet Dim old Val As String Dim new Val As String Dim str Sep As String Dim str Type As Long 'add comma and space between items str Sep = ", " If Target. Then, click the Contains command, to open the Custom Auto Filter box, and enter the text you want to filter.
By validating form responses before accepting them, we can alert users to their errors before they submit the form.