Macro to Insert row and protect som cells anywhere in the Worksheet

[graiggoriz]

Hello,

I need to run a macro that would insert me a row wherever the cell is active in the worksheet and to protect only from the inserted row the cells D*,F*:H*,J*,N*.

I recorded a macro but obviously the macro does not work anywhere in the worksheet:

Can you please help me to figure that out?

Thanks,
Graig

[Mumps1]

Do you want this to happen automatically when you select the cell or do you want to run a macro manually to do what you want? If you could post a copy of your file with a detailed explanation, it would be very helpful.

[graiggoriz]

I would like to manually execute the macro.

[Mumps1]

Start by protecting your worksheet without a password. If you want to use a password, the code has to be modified to include the password. Click on any cell to make it active and then run the macro. All the cells in the inserted row will be available for data entry except for those in columns D*,F*:H*,J*,N*.

Please Login or Register  to view this content.

[graiggoriz]

It works well!! Many thanks. However, I forgot to say that the inserted rows need to be in a Table1.

Is there any way to protect the entire column D, F, G, H, J, N INSIDE the Table1 ?? Otherwise when I run your macro it removes the protection I have in the table in the "column" D, F, G, H, J, N (at least it remove the protected cells from the table1).

Thanks

[graiggoriz]

Byw, My worksheet has as password and the protection should allow the user to:
Select Unlocked cells
Format cells
Format rows
Insert rows
Delete rows

Thanks

[Mumps1]

Try this macro. It will protect the entire columns D, F, G, H, J, N and allow you to format the cells. I don't believe that Excel allows you to manually insert and delete rows on a protected sheet. It can only be done through VBA.

Please Login or Register  to view this content.

[graiggoriz]

Thanks. I got the line:

in error

[Mumps1]

I tried the code on a dummy sheet and I didn't get any error. Can you post a copy of your file?

[graiggoriz]

Here is the sample file. I should be clearer this way :-D.

If you do a CTRL + T. It will excecute the macro you gave me.

Please let me know if you do not get an error.

Thanks,
Graig

[graiggoriz]

Later on in that file, I will probably need to create another macro to cut and paste rows...

So, if you know a macro to protect back in the table1 the column 3, 5, 6, 7, 9, 13 or (D28:D70;F28:H70;J28:J70;N28:N70) from the attached file I would be able to implement it in th below macro:

Thanks,
Graig

[Mumps1]

Hi Graig. I still get the error. I could not see the first 11 rows of your sheet. Is it possible that you have merged cells anywhere in column J?