Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automation excel to access | Excel Programming | |||
Visible automated Excel: automation crashes if user selects cells, protection? | Excel Programming | |||
Automation between Access and Excel | Excel Programming | |||
Access Automation to Excel | Excel Programming | |||
Automation from .pdb to excel and then to access | Excel Programming |