Automation of worksheet protection from Access VBA - can't insertrows afterwards
 

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").Formula Hidden = 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?

Automation of worksheet protection from Access VBA - can't insertrows afterwards
 

On Jul 2, 4:18*pm, Mangetout 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").Formula Hidden = 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