Applying Data Validation to Column

Question

I have an excel table within a protected worksheet. I am trying to do the following steps:

1. Add a new column called "decision"
2. unlock the cells within column "decision"
3. Filter the table based on values in another column "sold" (if a row says error, keep it visible and hide all others"
4. Apply data validation to all visible cells to allow only 2 values "delete" or "classify"

I have the following code below. When i run it, i don't get any errors but there is no validation in the expected cells and the cells are not unlocked. Also, i am thinking that the unlock should probably come after the filtering but open to thoughts.

    Private Sub HandleErrors()

    Dim ws, wsList As Worksheet
    Dim importTable As ListObject
    Dim DecisionColumn As ListColumn
    Dim listRange, valRange, cell As Range
    Dim listOptions As String

    Set ws = Worksheets("Sales Import")
    Set importTable = ws.ListObjects("Table_SalesImport")
    Set wsList = ThisWorkbook.Worksheets("Lists")
    Set listRange = wsList.Range("A1:A2")
    Set cell = ws.Range("W2")
    listOptions = "Delete, Classify"

    ws.Unprotect Password:=PassW

    With importTable

        ws.Unprotect Password:=PassW

        Set DecisionColumn = importTable.ListColumns.Add
        DecisionColumn.Name = "Select Decision"

        Range("W2").Select
        Set valRange = Range(cell, cell.End(xlDown))
        valRange.Locked = False
        valRange.Locked = False

        If .Parent.FilterMode Then .Range.AutoFilter
 
        .Range.AutoFilter Field:=22, Criteria1:="Error"

        With valRange.Validation
            .Delete 'delete previous validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Formula1:=listOptions
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = False

        End With

        Application.Run "Protect"

    End With

    End Sub

Answer 1

Changes:

• Retrieve the range of the newly added column (excluding the header) using DecisionColumn.DataBodyRange.

• Obtain the visible range using valRange.SpecialCells(xlCellTypeVisible).

• listOptions can come from the wsList sheet.

• Dim listRange, valRange, cell As Range – listRange and valRange are declared as Variant variables.

• Set cell = ws.Range("W2") is executed before adding a column, so after the column is added, cell refers to X2. I assume this is not the intended behavior.

• It's worth to read How to avoid using Select in Excel VBA

Option Explicit
Private Sub HandleErrors()
    Dim ws, wsList As Worksheet
    Dim importTable As ListObject
    Dim DecisionColumn As ListColumn
    Dim valRange As Range
    Dim listOptions As String
    Set ws = Worksheets("Sales Import")
    Set importTable = ws.ListObjects("Table_SalesImport")
    Set wsList = ThisWorkbook.Worksheets("Lists")
'    listOptions = Join(Application.Transpose(wsList.Range("A1:A3").Value), ",")
    listOptions = "Delete,Classify"
    Dim PassW As String: PassW = "123" ' modify as needed
    ws.Unprotect Password:=PassW
    With importTable
        If .Parent.FilterMode Then .Range.AutoFilter
        Set DecisionColumn = importTable.ListColumns.Add
        DecisionColumn.Name = "Select Decision"
        Set valRange = DecisionColumn.DataBodyRange
        valRange.Locked = False
        .Range.AutoFilter Field:=22, Criteria1:="Error"
        Dim visRange As Range
        On Error Resume Next
        Set visRange = valRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not visRange Is Nothing Then
            With visRange.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=listOptions
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = False
            End With
        End If
    End With
    ws.Protect Password:=PassW
End Sub

Microsoft documentation:

ListColumn.DataBodyRange property (Excel)

Range.SpecialCells method (Excel)