I have a worksheet with locked columns containing formulas. how can i insert rows & copy the formula above without having to unprotect the worksheet.
I have a worksheet with locked columns containing formulas. how can i insert rows & copy the formula above without having to unprotect the worksheet.
In some versions of excel, you can allow the user to insert rows or columns when you protect the sheet. Make sure that all the rows/columns in that allowed range are unlocked.
But I need to have locked cells stay that way to prevent other users from changing the formulas and data in those cells. i tried recording a macro but I can't bypass unprotected the sheet. by the way, I'm using excel 2007.
You could add some code to your macro:
activesheet.unprotect password:="topsecret"
'your code here
activesheet.protect password:="topsecret"
If the objective is to manually insert rows into the document after protection from XL2002 (I think, and perhaps also from XL2000), it is possible to protect the sheet, leaving the possibility to insert rows (via the Insert menu), see the available options box at the manual protection of the sheet. In VBA, you can also do (in the launch of the workbook in the Workbook_Open event): ActiveSheet.Protect Password = 'password', AllowInsertingRows: = True Moreover, if the goal is to insert rows in VBA, you can use the method Unprotect / Protect (as indicated by Robert that I welcome the way), but you can also use the protection sheet by VBA always launch of the workbook:
ActiveSheet.Protect Password = 'password', UserInterfaceOnly: = True. With this argument, the changes are authorized by VBA code (without having to unprotect) but not allowed to the user.
To insert lines is sufficient to check the lines to enter when the sheet protectors. To delete rows (Delete rows in protected sheet does not work), I use this macro to be inserted in a standard module:
Code:Public Sub EliminaRige () ActiveSheet.Unprotect Password: = "abc" Selection.Delete Shift: = xlUp ActiveSheet.Protect Password: = "abc" end Sub
Bookmarks