Code to add data validation throws no error, but also does not work

Question

I'm trying to use VBA to add a drop down list that can vary in length dependent on the size of an array. I'm getting no error for the following code, but it also does not actually add the data validation...

To form the list:

Dim RegionList as String
RegionList = ""
For i = LBound(x) To UBound(x)
    RegionList = RegionList + x(i) & ", "
Next

To create the validation:

RegionList = Left(RegionList, Len(RegionList) - 2) 'Remove trailing Comma/Space

With Sheet2.Range("C1").Validation 'Insert dropdown to cell C1
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=RegionList
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

I should note that the sheet is protected prior to running this code with the following formula:

Sheet2.Protect Password="xxx", DrawingObjects = False, UserInterfaceOnly = True

Edit: The specific cell where I want the drop down list to appear is unprotected, so the bit about protecting the sheet is probably irrelevant.

Any ideas would be appreciated!

Validation will only 'kick in' on subsequent changes to the cell. — Bathsheba
– Bathsheba, Commented May 29, 2013 at 10:51
Bathsheba, I don't follow completely. What do you mean by subsequent changes to the cell? Right now if the cell holds the value 'HI' (for example), if I change it to 'HO', the validation does not 'kick in' and no drop down appears. — Sam WB
– Sam WB, Commented May 29, 2013 at 11:13
I think what @Bathsheba means is that you need to set up an event handler. Something like Private Sub Worksheet_SelectionChange(ByVal Target As Range). In other words, what is telling your code to perform this action? Do you have a button, another event handler, etc? — Gaffi
– Gaffi, Commented May 29, 2013 at 11:46
This code sits inside a sub that runs when the workbook is opened. Edit: The options in the list change based on the windows username of who is using the workbook. — Sam WB
– Sam WB, Commented May 29, 2013 at 11:56

Kazimierz Jawor · Accepted Answer · 2013-05-29 12:42:26Z

3

You can't add cell validation feature when your sheet is protected. Even if you set parameter userInterfaceOnly:=true it is still not allowed.

You need to move your protection code after you add validation to your cell. If it's not enough you could temporarily unprotect your sheet just before setting validation and set it back right after.

answered May 29, 2013 at 12:42

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

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

3 Comments

Sam WB Over a year ago

Does this mean that you can't set any cell validation features? What if the specific cell that's supposed to hold the validation is unlocked? Haven't had a moment to check this via my worksheet just yet, but wanted to ask.

Kazimierz Jawor Over a year ago

it seems that 'validation' is rather more sheet feature than cell. Therefore locking/unlocking cell has nothing to do with that. I did checked it hence my recommendation.

Sam WB Over a year ago

Was able to check this morning and you are correct. Putting in the validation prior to protecting the sheets solves the issue. Thanks!

Collectives™ on Stack Overflow

Code to add data validation throws no error, but also does not work

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related