Prevent others from unhiding columns, but still being able to insert and copy rows

[keykan]

Hi everyone.

First post on here, so please excuse me. I am trying to set up a spreadsheet that can be used by a variety of users. In my spreadsheet, users enter their data in column A for each row, formulas are calculated in columns B and C, and the results are displayed in column D. I want to keep my formulas safe, therefore I want to hide columns B and C and make it impossible for anyone to undo the hiding. However, users can still be allowed to insert and copy down in any rows.

I attempted using a few macros, but I was still unable to solve the problem. So kindly, if you guys could, assist me with this issue.

Thank!

 

[Wookiee]

welcome to the board!

i think your solution is easier than involving macros. if you want to protect your formulas, you can simply protect those columns.

first select your entire sheet and right-click
select Format Cells
go to the Protection tab
uncheck Locked
click OK

next, select columns B:C and right-click
select Format Cells
go to the Protection tab
check Locked
click OK

lastly, from the ribbon got to Review | Protect
Click Protect Sheet
(add a password if necessary)
click OK

now no one can touch your formulas unless they remove protection.

hope that's a solution for you!

 

[keykan]

Thank you for your comment.

But I already tried that but my problem still not solved. Users can't insert and copy rows. Here is an example

Users insert row 6 and want to expand the upper data sheet for one or two more rows. Like first post i said, i want to hide Column B and C, so they can't be messed. If i lock it, users can't be insert and copy paste to rows 6, 10 and go on. I cannot estimate how many rows of data there will be and below there is also another data sheet.

That is my problem.

 

[Wookiee]

But I already tried that but my problem still not solved. Users can't insert and copy rows. Here is an example

View attachment 115848


Users insert row 6 and want to expand the upper data sheet for one or two more rows. Like first post i said, i want to hide Column B and C, so they can't be messed. If i lock it, users can't be insert and copy paste to rows 6, 10 and go on.

you should be able to set it so that the user can insert rows in the Protect Sheet dialogue.

when i tested that on a file i mocked up, the copying and pasting of rows worked, but the formatting was thrown off because you have what appear to be merged cells in the Reference Value cells.

if that is the case, i think your best course of action would be to unmerge all the cells with Reference Value formula, then hide column B and use Protect Sheet.

 

[keykan]

I unmerged all the cell in the sheet, I also have checked to ensure that everything is allowed in the Protected Sheet setting.
And result: Rows can be inserted but can't be copied and pasted.

Haizzz.