I'm working on an access application that uses Excel and Outlook
automation to send spreadsheets by email,

Everything works, except that the sheets need their first 13 rows
protected (the header data), but need to permit the user to enter data
and insert rows into the detail part from the 14th row onward.

Starting with a completely unlocked, unprotected sheet, my code looks
like this:

'mark the cells locked:
objActiveWkb.Worksheets(1).Range("A1:Q13").Locked = True
objActiveWkb.Worksheets(1).Range("A1:Q13").FormulaHidden = False
'protect the sheet
'objActiveWkb.Worksheets(1).Protect ("MyPasswordHere"), DrawingObjects
= False, Scenarios = False, AllowInsertingRows = True,
AllowDeletingRows = True

I end up with a spreadsheet in which I can enter data into the detail
section (but not the first 13 rows, as desired), but I can's insert a
row anywhere in the sheet, even in the supposedly unprotected detail
section after row 14.

If I apply the protection manually, checking the Allow Deleting Rows
checkbox on the way, it works exactly as expected (but obviously I
can't expect the user to do this for the 200 spreadsheets the
application will send out each month).

What am I doing wrong?

On Jul 2, 4:18*pm, Mangetout <themanget...@hotmail.com> wrote:
> I'm working on an access application that uses Excel and Outlook
> automation to send spreadsheets by email,
>
> Everything works, except that the sheets need their first 13 rows
> protected (the header data), but need to permit the user to enter data
> and insert rows into the detail part from the 14th row onward.
>
> Starting with a completely unlocked, unprotected sheet, my code looks
> like this:
>
> 'mark the cells locked:
> objActiveWkb.Worksheets(1).Range("A1:Q13").Locked = True
> objActiveWkb.Worksheets(1).Range("A1:Q13").FormulaHidden = False
> 'protect the sheet
> 'objActiveWkb.Worksheets(1).Protect ("MyPasswordHere"), DrawingObjects
> = False, Scenarios = False, AllowInsertingRows = True,
> AllowDeletingRows = True
>
> I end up with a spreadsheet in which I can enter data into the detail
> section (but not the first 13 rows, as desired), but I can's insert a
> row anywhere in the sheet, even in the supposedly unprotected detail
> section after row 14.
>
> If I apply the protection manually, checking the Allow Deleting Rows
> checkbox on the way, it works exactly as expected (but obviously I
> can't expect the user to do this for the 200 spreadsheets the
> application will send out each month).
>
> What am I doing wrong?

Worked it out myself in the end - it needs to be either:
objActiveWkb.Worksheets(1).Protect Password :="MyPasswordHere",
DrawingObjects :=False, Scenarios := False, AllowInsertingRows := True

Or:
objActiveWkb.Worksheets(1).Protect "MyPaswordHere", ,
True, , , , , , , True, , , True