Work-around to insert rows with autofill in protected sheet with multiple formulas

[vpipalia]

Hi!

I have compiled a worksheet with a number of formulas in several columns as well as data cross-referenced to multiple worksheets. The cells with formulas are locked and the sheet is password protected. I have two macros running:
One is to allow grouping / ungrouping while the sheet is locked (stored in ThisWorkbook)

Please Login or Register  to view this content.

Second is to rename a sheet based on the cell value

Please Login or Register  to view this content.

Now, I have multiple sheets which are conditionally locked so that the end user has access to only those cells that require input and cannot edit any formulas or other data that they need not touch. However, on these sheets the users may require additional rows and while inserting rows is allowed, having the formulas inserted in the new row is the problem. I found this code on the internet:

This part goes in the relevant Sheet

Please Login or Register  to view this content.

and this part goes into a general module

Please Login or Register  to view this content.

The code works like a charm and inserts a new row with the formula above the cell when double clicking.

But, there are times when over 50-60 new rows need to be added (I just came across an instance where in one section of a sheet over 150 rows had to be added) and double clicking that many times is tedious (well atleast for the people using the sheet). Such data entry is not always required in the same sheet or even the same section of the a sheet and although extreme situations are rare, but this needs to be addressed nonetheless.

So I'm looking for the following added functions:

1. A macro linked to a button to insert rows above the row selected with all the formulas copied to the new row. The macro should prompt the user to enter the number of rows required and based on user input, insert the rows in the protected sheet above the selected row.

2. When the macro re-protects the sheet, the following actions should be allowed :
a. Selecting locked cells
b. Selecting unlocked cells
c. Format cells
d. Format columns
e. Format rows
f. Insert rows

4, The same password used for unlocking should be used for locking.

3. Such user input and hence this functionality is required in 3-4 different sheets. Is it possible to have a single macro linked to each of the buttons in these sheets?

Thanks for the help in advance. I am using excel 2013. Access to the VBA codes is also locked for the users.

[vpipalia]

Hi All!

sample sheet is attached. Please note the following:

- Password to VBA: 123456
- This is the simplified version of the sheet. The actual formulas are linked to other sheets.
- The sheet is divided into various sections and needs to be kept in the same form/ structure as data in other sheets is referenced on the same basis.
- The user may need the rows to be added in any section.
- This is just 1 of 17 other sheets in the workbook and 1 of the 4 which needs user input.
- User input is restricted to Columns A, B, C, D, G, J, O, R & V. Rest of the columns have formulas linked to other values in the workbook.
- The macro for inserting rows is added in the sample and works fine.
- Button to which macro is to be linked for inserting rows is also added.

Hope this clarifies any doubts.. Waiting for your responses!!

[vpipalia]

Any help would be most appreciated!!Really need to get this project finished!!