EN VI

Excel cell validation set by vba sets incorrect data range?

2024-03-15 22:30:04
How to Excel cell validation set by vba sets incorrect data range

I have a procedure which sets the validation list for cells in a range. I finally got it to work, getting the correct range to be set as validation list. But when I check the validation settings, the range is different. And I cannot understand what and why this happened.

Below is the messagebox telling me the range for the validation list:

correct validation list range

But below is what the validation list range is that has been set:

actual validation list range

I just do not understand why this range is changed to A2:A7 instead of a1:a6.

This is the code for the procedure:

Sub validatie()
Dim ws As Worksheet, ws1 As Worksheet
Set ws = ThisWorkbook.Worksheets("Hoofdbestand")
Set ws1 = ThisWorkbook.Worksheets("Verwijzingen")

aantalrijen2 = ws1.Range("A1", ws1.Range("A1").End(xlDown)).Cells.Count

With ws

aantalrijen = ws.Range("A1", ws.Range("A1").End(xlDown)).Cells.Count
    With .Range("B2:B" & aantalrijen).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & ws1.Name & "!" & "A1:A" & aantalrijen2
    MsgBox "=" & ws1.Name & "!" & "A1:A" & aantalrijen2
    End With
End With

End Sub

Solution:

Lock the row references using $:

Formula1:="=" & ws1.Name & "!" & "A$1:A$" & aantalrijen2

Or just make the whole reference absolute:

Formula1:="=" & ws1.Name & "!" & "$A$1:$A$" & aantalrijen2
Answer

Login


Forgot Your Password?

Create Account


Lost your password? Please enter your email address. You will receive a link to create a new password.

Reset Password

Back to login