allow macro to add data to a protected cell

[dsrt16]

I have a loop code which enters a formula into each worksheet 3-31 in cell A17.

However, I need to protect cell A17, so no user enters data or deletes the results of the formula on accident.

I know I how to write a macro to allow this through the context of setting a certain worksheet.

Dim Wks As Worksheet

    Set Wks = Worksheets("My Data")

Wks.UnProtect Password:="password here"

But I don't know to do it in context of my current loop code:
The code

Dim i As Integer

    For i = 3 To 31
        
        Sheets(i).Range("A17").FormulaR1C1 = "=PULL(""'X:\Staff\AI Program\""&'Set-up Page'!R[-11]C[4]&""\[""&'Set-up Page'!R[-11]C[5]&""]""&R[-15]C[2]&""'!A17"")"
       

    Next i

Cell A17 needs to return the results of a formula, but it needs to be protected.

 

[Yahya]

if i understand you
you need to loop throw all sheets
try this

Sub Test()
Dim Wks As Worksheet
Dim i As Integer

For Each Wks In Sheets

Wks.Unprotect Password:="password here"
For i = 3 To 31
     Sheets(i).Range("A17").FormulaR1C1 = "=PULL(""'X:\Staff\AI Program\""&'Set-up Page'!R[-11]C[4]&""\[""&'Set-up Page'!R[-11]C[5]&""]""&R[-15]C[2]&""'!A17"")"
Next i

Wks.Protect Password:="password here"
Next Wks
End Sub

 

[dave3009]

Yahya

You could avoid the double loop in the code, like so..

Sub Test()
Dim i As Integer
For i = 3 To 31
     With Sheets(i)
        .Unprotect Password:="password here"
        .Range("A17").FormulaR1C1 = "=PULL(""'X:\Staff\AI Program\""&'Set-up Page'!R[-11]C[4]&""\[""&'Set-up Page'!R[-11]C[5]&""]""&R[-15]C[2]&""'!A17"")"
        .Protect Password:="password here"
    End With
Next i
End Sub

 

[dsrt16]

Dave, that worked great! Thanks!