Error 400 when sheet is protected

**Master Foo** · 07-03-2013, 03:00 PM

When using the code below it works perfectly with the sheet Not protected but after protecting the sheet it gives (error 400) and opens the VB Editor after pressing the button but it still puts the correct values in the cells. The cells that are used are not locked but other adjacent cells are. I can't figure out why it is giving that error or how to correct it so that it does not give the error with the sheet protected.

-----------

Sub Dates()

Dim i As Integer
Range("A5").Select
For i = 1 To 7
ActiveCell.FormulaR1C1 = "=TODAY() - WEEKDAY(TODAY()*0.2)+" & i
ActiveCell.Offset(1, 0).Select
Next i
Range("A5:A11").NumberFormat = "DDDD ~ m/d/yy"

End Sub

**shg** · 07-03-2013, 03:39 PM

You code will try to select A12, which won't work if it's locked and the protection disallows selection of locked cell. But there's no need to select:

Sub Dates()
    Dim i           As Long
    

    For i = 1 To 7
        Range("A5")(i).FormulaR1C1 = "=TODAY() - WEEKDAY(TODAY()*0.2) + " & i
    Next i

    Range("A5:A11").NumberFormat = "DDDD ~ m/d/yy"
End Sub

**Master Foo** · 07-03-2013, 04:06 PM

shg - that still gives error 400 when the sheet is protected. I had previously unlocked cell A12 thinking the same thing but still got the error when the sheet was protected.

**jolivanes** · 07-03-2013, 03:47 PM

Does this work?

Sub Dates()
    Dim i As Integer
    ActiveSheet.Unprotect ("Password")    '<----- Change to actual password
    Range("A5").Select
    For i = 1 To 7
        ActiveCell.FormulaR1C1 = "=TODAY() - WEEKDAY(TODAY()*0.2)+" & i
        ActiveCell.Offset(1, 0).Select
    Next i
    Range("A5:A11").NumberFormat = "DDDD ~ m/d/yy"
    ActiveSheet.Protect Password:="Password", _
                        UserInterFaceOnly:=True
End Sub

Re: You code will try to select A12 (shg)
Did not notice that.