Validation error when worksheet is protected

[Tom Emmery]

Hi,

Below code works fine as long as I do not protect my workbook, but gives an
error when protected. How to solve this ?

Runtime error '-2147417848 (80010108)'
Method 'Modify' of object 'Validation failed'

With Worksheets(1).Range("G14").Validation
.Modify xlValidateDecimal, xlValidAlertStop, xlBetween, "1.0", "7.0"
.ErrorMessage = "Enter value between 1,0 and 7,0" + EOL + "Use end of
line"
.ShowInput = False
.ErrorTitle = "Invalid input"
.ShowError = True
End With

I've already tried ActiveWorkbook.Unprotect, Add Type iso Modify and
Validation.delete before add / modify. None of these alternatives seem to
work.
Cell G14 is not locked and not hidden.

Why is protect workbook causing this error, and how to solve this ?

 

[Tom Ogilvy]

If you unprotect your workbook in code, execute the command and reprotect the
workbook (all in code) and you say that a protected workbook is the cause of
the problem, then that should fix it.

 

[Tom Emmery]

Hi Tom,

I've tried unprotect before (and protect after) below code, but this doesn't
change the error.

ActiveWorkbook.Unprotect

Or should I use other code to resolve ?

Greets,

Tom