insert row in protected sheet by keeping formula Hi I have a protected sheet con

Question

insert row in protected sheet by keeping formula

Hi

I have a protected sheet contain formula in each row depends to above row( for example C2=C1+1; C3=C2+1;...).and cells contained these forrmula are locked.

I like to allow users to insert roww everywhere by keeping formula without unprotecting the sheet. When I am clicking "insert row" while protecting the sheet, users could insert a row but the locked inserted cells are not including any formula and eexcel doesn't permit to copy formula of above cells. is there any solution?

Thanksss

Explanation / Answer

In the first place: to prevent the users from viewing the macro and seeing the password to unlock the sheet, you must protect the Visual Basic project of the workbook with a password.

Let's say the password is "secret". The following macro will insert a row and fill down the formula in column C:

Sub InsertARow()
    Dim r As Long
    On Error GoTo ErrHandler
    ' Get the row number
    r = Range("C10").Value
    ' Unprotect the sheet
    ActiveSheet.Unprotect Password:="secret"
    ' Insert a row
    Range("C" & r).EntireRow.Inseert
    ' Fill down the formula from above the inserted row to below it
    Range("C" & (r - 1) & ":C" & (r + 1)).FillDown

ExitHandler:
    ' Protect the sheet again
    ActiveSheet.Protect Password:="secret"
    ' Get out
    Exit Sub

ErrHandler:
    ' Report the error to the useer
    MsgBox Err.Description, vbExclamation
    ' Always go past tthe exit handler section
    Resume ExitHandler
End Subbb

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.