Excel Data Validation in VBA - not showing error message if value is other than specified list?-CodePudding

After running below piece of code, data validation is added to the cells I want, but whenever I type in another value other than the list, no error message is showing up. I want users only to be able to fill in data based on my specified list. How can I do this?

I tried to add the Operator xlEqual and xlBetween, but I can't seem to get it to work.

After running the macro, I can select e.g. following options for the cells from my data validation list:

These are the only options I can choose, but whenever I write in for example "C" in one of the cells it still accepts that value, even though it's not part of my data validation list.

Sub ChangeVersionDetails()

Application.ScreenUpdating = False

    Dim LastRow As Long
    Dim LastRowOne As Long
    Dim LastRowEnd As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    LastRowOne = Cells(Rows.Count, 1).End(xlUp).Row   1
    LastRowEnd = Cells(Rows.Count, 1).End(xlDown).Row
    
    Range("G11:G" & LastRow).Validation.Delete
    Range("I11:T" & LastRow).Validation.Delete
    Range("G11:G" & LastRow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=DataVStatus"
    Range("I11:T" & LastRow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=DataVInvoicingMonths"
    Range("G" & LastRowOne & ":G" & LastRowEnd).Validation.Delete
    Range("I" & LastRowOne & ":T" & LastRowEnd).Validation.Delete

End Sub

CodePudding user response：

Set IgnoreBlanks to False.

Sub ChangeVersionDetails()

    Dim LastRow As Long
    With ThisWorkbook.Sheets("Sheet1")
        .Range("G:G,I:T").Validation.Delete
        
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        With .Range("G11:G" & LastRow).Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=DataVStatus"
            .IgnoreBlank = False
        End With
        
        With .Range("I11:T" & LastRow).Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=DataVInvoicingMonths"
            .IgnoreBlank = False
        End With
        
    End With
    
End Sub