I have a worksheet that is password protected so I found that I could use the UserInterFaceOnly property to allow my VBA code to make changes to my sheet whilst keeping the sheet protected.
This works up till a point, I can make changes through my code but it bugs out at one point below. The line .Add xlValidateList ..... is where the bug appears. The error message is Method ‘Add’ of object ‘Validation’ failed. Now when I unprotect the sheet and take out the UserInterFaceOnly property the same code below works perfectly!
Set RngListBox = Range("C" & i)
.Add xlValidateList, xlValidAlertInformation, xlBetween, ListWeight
.InCellDropdown = True
Any help would be great, thanks