Add row to table in Excel 2010 Password Protected Sheet with formulas

**vew32** · 05-21-2012, 12:04 PM

I have a Table with locked, unlocked and drop down box cells within it. This is because they contain formulas. Drop down box's to help with formulas. the sheet is locked to avoid tamper with the formulas

i have created a button to press which inserts a row at the bottom of the table with all the formals etc

i can insert a new row using this (when document is not password protected)

Selection.ListObject.ListRows.Add AlwaysInsert:=True
Range("A191").Select

but i need to add something like

ActiveSheet.Protect Password:="password"

can some one help with this please

thanks

**bonny24tycoon** · 05-21-2012, 12:16 PM

Hi There,

You would need to do the below -

1. Protect your sheet with password as "password"

2. You would then need to enter the below code on your macro where you need the macro to unprotect the sheet, so that a row is added.

ActiveSheet.Unprotect Password:="password"

3. You would then need to enter the below code on your macro where you need the macro to again protect the sheet.

ActiveSheet.Protect Password:="password"

Or paste your entire macro here, so I can help you if its confusing

Let me know if it worked.

Thanks,

Bonny Tycoon

**If I was able to help please click the small star icon at the bottom left of my post **

**vew32** · 05-22-2012, 05:35 AM

thanks for the above it makes perfect sense

i can use the below to add a row to a table with locked cells on a protected sheet and all formulas from above go into the new row as well

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="password"
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    ActiveSheet.Protect Password:="password"
End Sub

thanks for your help

**kinsley** · 08-10-2012, 02:51 PM

How can I add to unlock just the newly inserted row so the user can add data?

Thanks,
-Don

**Ismaili** · 03-08-2013, 06:14 PM

HI,

I used the code but it gives me a runtime error 91 object variable or with block variable not set. any help will be appreciated

**arlu1201** · 08-10-2012, 03:54 PM

Kinsley,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.