Data Validation using VBA code

[gototcm]

The code below generates a dynamic drop down list in Excel 2007. For simplicity, the source list is in the WS "_BfrBNam" and consists of 3 choices (A, B, C). The drop down list appears on the WS "Actual" in cells B3-B1500. All works well except if an entry in cells B3-B1500 is NOT A,B or C there is no error message generated. This can be fixed by unchecking the Ignore Blank on the Data Tools tab, Data Validation, Settings. The question is how can the code below be modified to uncheck this box which appears to be normally checked.

I hope this is clear.

#
Dim rng As Range
Set rng = Sheets("Actual").Range("B3:B1500") ' This is the validation range.
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, _
Operator:=xlBetween, Formula1:="=_BfrBNam" 'This is the list containing A, B or C
End With

#

[TMS]

Try:

Please Login or Register  to view this content.

[gototcm]

Yep, that worked. After I saw your corrections it became obvious. Thanks.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.