insert row in protected sheet by keeping formula

Anonymous

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 formula are locked.

I like to allow users to insert row 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 excel doesn't permit to copy formula of above cells. is there any solution?

Thanks

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.7K MVP Volunteer Moderator

If you just want the result of the formula, try

ActiveSheet.Range("E" & r).Value = ActiveSheet.Range("E1").Value

0 comments

Answer accepted by question author

HansV 462.7K MVP Volunteer Moderator

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.

In the Visual Basic Editor, select Tools | VBAProject Properties...
Activate the Protection tab.
Tick the check box "Lock project for viewing".
Enter a password in the Password box.
Enter the same password in the Confirm password box.
Important: don't forget this password!
Click OK.
Save the workbook.
Next time you open the workbook and want to view the Visual Basic code, you'll be prompted for the 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.Insert

' 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 user

MsgBox Err.Description, vbExclamation

' Always go past the exit handler section

Resume ExitHandler

End Sub

0 comments

41 additional answers

Anonymous

2014-08-02T23:45:42+00:00

Actually I came back at this fresh and started messing with it again and it worked. Whoo Hoo! Thanks a lot. I had been banging my head for a while on this one.
Was this answer helpful?

0 comments No comments
HansV 462.7K Reputation points MVP Volunteer Moderator

2014-08-03T09:19:49+00:00

Good to hear that. Thanks for the feedback.
Was this answer helpful?

0 comments No comments
Anonymous

2014-08-05T15:12:04+00:00

Hey HansV

Sorry to bother you again but when I actually go to put the code in the spreadsheet it doesn't work. Says it needs to de bug. When I got ot it this is what it shows (ok thought this thing had highligting, but basically what I have bolded is what is highlighted when excel teels me what needs to be fixed) but I don't know what i should do to fix this.

Option Explicit

Sub InsertRow()

    Const HeaderRow = 5

    Dim CurRow As Long

    Dim LastRow As Long

    CurRow = ActiveCell.Row

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    If CurRow <= HeaderRow + 1 Or CurRow >= LastRow Then

        Beep

        Exit Sub

    End If

    ActiveSheet.Unprotect Password:="secret"

    Cells(CurRow, 1).EntireRow.Insert

  Rows(CurRow - 1).SpecialCells(xlCellTypeFormulas).Resize(2).FillDown

    ActiveSheet.Protect Password:="secret"

End Sub

This tested out fine at home buit now that I am trying to get it to work on the actual spreadsheet at work it wont. I would guess it might have to do with the formulas cause this is a far bigger sheet, but I don't know why it would. It inserts the row and unprotects the sheet but doesn't fill the formulas in or reprotects the sheet.
Was this answer helpful?

0 comments No comments