Copy/paste/cut/insert rows across protected/locked columns

Hi guys,

I'm having a lot of trouble finding a solution to this issue, so I hope you can help me.

Two columns in my sheet have formulas that I do not want users to edit, including the values created by those formulas. However, users want to be able to copy, paste, cut, and insert ROWS from one place to another anywhere in the sheet.

For example, row 2 needs to be pasted into row 3, but columns P and W are locked for editing, so excel throws an error.

If there is a macro or VBA code I can add to the sheet to achieve this I would be very grateful to know what it is. None of the code I've tried so far has helped. My current workbook is attached and locked with no password.


MBP - Statistics.xlsm

Originally Posted by AlphaFrog

Instead of password protecting the two columns, you could have a Worksheet_Change event procedure to re-apply the formulas to columns Q and W if they are changed.

While this solution is not 100% ideal (the user could get stuck in an incorrect formula loop if they accidentally edit it), it certainly achieves the function I was looking for.

Thank you very much; you are indeed the alpha frog.

How about using an application.inputbox set to accept a range.
Set your protection to allow a user to select locked cells.
When the macro runs it can prompt them to type or choose the range they want to copy and then type or choose the range to paste it into, it then unlocks the sheet, pastes the code and relocks the sheet, this happens in a fraction of a second.

You can restrict the range they can select, copy from and copy to with the code, using the intersect method.

Application.inputbox, you are interested in type 8 with which you can either type in a range or select the range.

Thanks for your reply. Would this solution retain the functionality of copy/paste without additional steps? I don't think users would find it easy to input cell ranges instead of the usual right-click route.

Originally Posted by AlphaFrog

Instead of password protecting the two columns, you could have a Worksheet_Change event procedure to re-apply the formulas to columns Q and W if they are changed.

Please Login or Register  to view this content.

This worked perfectly until two new sheets were added to the workbook and the original was renamed. Additionally, I added some new columns and moved others, resulting in the original columns Q & W becoming R & X.

To get around the difference in columns, I modified the code somewhat, but the functionality has completely stopped working. Here's my modification:

Please Login or Register  to view this content.

Please could you let me know what can be done to rectify it, and perhaps how I could change it again if the decision is made to add more columns/sheets. Thank you very much.

Originally Posted by AlphaFrog

Try saving and closing the workbook. Then restart Excel and try it again.

I suspect you edited the original code and had an error. Then you fixed the error. That's when the code stopped working. That's because this line Application.EnableEvents = False suspends the code from triggering so the next line can change the worksheet and not trigger the code again.

If you restart Excel, that should fix it.

Before you save, add these lines. That will prevent it from having this problem again

Please Login or Register  to view this content.

The functionality now works again (thank you) but I'm struggling to get column X to generate the correct data.

While the restart and the addition of this code has helped with column R (with the formula now drawing data from columns N & Q), column X is having issues. X should have this formula: =IF(OR(V2>TODAY(),AND(W2="",V2="")),"",IF(W2="",DATEDIF(V2,TODAY(),"d"),W2-V2))

But instead shows this one: =IF(OR(T2>TODAY(),AND(W2="",T2="")),"",IF(W2="",DATEDIF(T2,TODAY(),"d"),W2-T2))

Also, how would I write the above code if I need to have the same functionality on another sheet? I'm guessing it has something to do with "FormulaR1C1 = _" clause, but I don't know how to amend that for various columns (it took some codebreaking to understand that [-4] and [-1] was the way to fix column R, but now I stumped).

Excuse my ignorance, but I'm an relative Excel noob compared to most folks in here.

Originally Posted by AlphaFrog

This uses two different formulas for R and X

Please Login or Register  to view this content.

R1C1 References

SO awesome, thanks. I can now use the single R1C1 refs for other sheets. You're a godsend. Thank you. I'll study up on the reference systems.