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

Question

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:

A
B

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

I'm sorry, what do you mean? You can copy everything I wrote right? Haha. — DrKedi
– DrKedi, Commented Dec 24, 2022 at 15:02
please give examples of what you are typing in and what your validation criteria are so we can try it out — MT1
– MT1, Commented Dec 24, 2022 at 15:07
Thanks for trying to help! I've added a bit of text to my post, because I'm not allowed to post pictures here yet. Does that help clear my issue up? — DrKedi
– DrKedi, Commented Dec 24, 2022 at 16:07
Are there are blank cells at the bottom of the validation list ? — CDP1802
– CDP1802, Commented Dec 24, 2022 at 16:32

CDP1802 · Accepted Answer · 2022-12-24 17:31:21Z

2

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

answered Dec 24, 2022 at 17:31

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CDP1802 Over a year ago

@DrKedi Usinq unqualified ranges like Cells(Rows.Count, 1).End(xlUp).Row is not recommended because they are the source of many VBA bugs. If you are just starting to program I recommend you avoid it wherever possible.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related