(Very?) strange code behavior

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

(Very?) strange code behavior

Post by ErikJan »

Code: Select all

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .Visible = True
            .Unprotect Password:=Password
            .Protect Password:=Password, UserInterfaceOnly:=True
        End With
    Next
        With Range("I_Tstart").Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertInformation, _
             Operator:=xlBetween, Formula1:="1/Jan/" & Range("CurYear"), Formula2:="31/Dec/" & Range("CurYear")
Events disabled.No active error trapping.

If I run this code, the Validation.Delete works, but there is an error on the Validation.Add. However, if I comment out the ".Protect" statement above, all works fine.

I'm lost... There were other things I could explain. Could it be that my WB is somehow corrupt? Or am I not seeing something?

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: (Very?) strange code behavior

Post by HansV »

Range(...) refers to the active sheet, not to ws. Is that intentional? If not, use ws.Range(...)
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: (Very?) strange code behavior

Post by HansV »

You might also try protecting the sheet AFTER setting data validation.
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: (Very?) strange code behavior

Post by ErikJan »

Thanks. The first loop does all, these are pieces from code, the Validation is correct and it works as the DELETE gives no error but the ADD does (and if I don't protect, the ADD gives no error so the statement itself is OK).

Sure I can try to change order but this doesn't make sense... right?

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: (Very?) strange code behavior

Post by HansV »

Specifying UserInterfaceOnly:=True hasn't always worked for me - some features apparently require the sheet to be unprotected.
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: (Very?) strange code behavior

Post by ErikJan »

And THAT was the response I was hoping NOT to get... (even though I know you are right probably). Fact is that I haven't seen this fail before and the odd thing is also (and I will try this) that this WILL work if I put only this piece of code in a new WB and test (but I'll confirm)

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: (Very?) strange code behavior

Post by ErikJan »

I stand corrected... the code fails indeed when I allow the ".Protect" and works when I comment that out (it's odd as the ".Delete" part also changes the cell and that is allowed somehow no matter if I set the protection or not)

Guess I'll have to find ways to work around that in my code

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: (Very?) strange code behavior

Post by ErikJan »

Here's simplified code proving the concept

Code: Select all

Sub t()
    ActiveSheet.Unprotect Password:=""
    'If the next line is commented out there's no error in the ".Add" below but the ".Delete" always works
    ActiveSheet.Protect Password:="", UserInterfaceOnly:=True
    '
    Range("A1").Validation.Delete
    Range("A1").Validation.Add Type:=xlValidateWholeNumber, Operator:=xlGreater, Formula1:="0"
End Sub