VBA- Data Validation error

Question

I have a protected sheet , but the cells B12:B27 are not locked . this code is not working with locked sheets. any idea?.

If Not Application.Intersect(Target, Range("B12:B37")) Is Nothing Then
    With Range("B" & ActiveCell.Row).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="B,E,R,TG,TU,V"
    End With
End If

Thanks

Dave · Accepted Answer · 2014-09-26 07:36:20Z

2

When protecting the worksheet you have the option to allow deleting rows or deleting columns. Deleting individual cells is not an option because it could affect locked cells.

The .Delete command will remove the cell entirely, meaning the existing cells below or to the right will either need to be moved up or left respectively. If this is what you want to do, you will need to unprotect your sheet first.

ActiveSheet.Unprotect
' your code
ActiveSheet.Protect

If you just want to clear the contents of the cell, you can use

.ClearContents

answered Sep 26, 2014 at 7:36

Dave

1,6431 gold badge9 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

VBA- Data Validation error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related