no move and paste

Aries · Mar 20th, 2002, 02:49 AM

I have a worksheet with many formulas. Can I restrict the users from moving and pasting cells (paste values only is allowed)? I can only lock cells with formulas. Cells for data input must be unlocked.

Dave Hawley · Mar 20th, 2002, 03:03 AM

Hi Aries

All cells are Locked by default, but this has no effect until the sheet is protected, Tools>Protection>Protect Sheet.

The cells you want to allow data entry into need to be Unlocked via the Format dialog.

Simple way is this.

Select any single cell
Push F5 and click Special
Select "Constants"
Click Ok
Go to Format>cells>Protection.

Aries · Mar 20th, 2002, 03:21 AM

Sorry, I don't understand your point.
I want to prevent the users from moving the cells for data input as it will change the address in the formulas in other cells.
I am not familiar with the goto (F5) function, how can I acheive my goal with it?

Audiojoe · Mar 20th, 2002, 03:33 AM

Another way to Format the cells is to:

Select the cells you want to unlock (or allow your users to modify, such as data input cells)
Right Click and select Format Cells
Click on the Protection Tab
Uncheck the box marked Locked
Click OK

Now protect the sheet as normal and the only cells your users will be able to change are the ones you unlocked

[ This Message was edited by: Audiojoe on 2002-03-20 02:34 ]

Aries · Mar 20th, 2002, 03:45 AM

I know how to unlock the cells.
But what I want to do is:
I have a rectangular range of cells unlocked. Users can input data into the cells. But if they mistakenly input data into wrong cell (say the next column, which is also an unlocked cell), they tries to move the cells to the correct address. I want to prohibit these moves.

Dave Hawley · Mar 20th, 2002, 03:47 AM

The formala reference will only change if they "Cut" the cell. This is by design if you prevent this from happening the consqueces may be worse! However if this is what you want there is one non VBA approach I can think of. This is to enclose all cell references in your formulas with the INDIRECT function
=INDIRECT("E1")

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 02:49 AM	#1
Aries New Member Join Date: Mar 2002 Location: Hong Kong Posts: 11	I have a worksheet with many formulas. Can I restrict the users from moving and pasting cells (paste values only is allowed)? I can only lock cells with formulas. Cells for data input must be unlocked.

Mar 20th, 2002, 03:03 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Aries All cells are Locked by default, but this has no effect until the sheet is protected, Tools>Protection>Protect Sheet. The cells you want to allow data entry into need to be Unlocked via the Format dialog. Simple way is this. Select any single cell Push F5 and click Special Select "Constants" Click Ok Go to Format>cells>Protection.

Mar 20th, 2002, 03:21 AM	#3
Aries New Member Join Date: Mar 2002 Location: Hong Kong Posts: 11	Sorry, I don't understand your point. I want to prevent the users from moving the cells for data input as it will change the address in the formulas in other cells. I am not familiar with the goto (F5) function, how can I acheive my goal with it?

Mar 20th, 2002, 03:33 AM	#4
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Another way to Format the cells is to: Select the cells you want to unlock (or allow your users to modify, such as data input cells) Right Click and select Format Cells Click on the Protection Tab Uncheck the box marked Locked Click OK Now protect the sheet as normal and the only cells your users will be able to change are the ones you unlocked [ This Message was edited by: Audiojoe on 2002-03-20 02:34 ]

Mar 20th, 2002, 03:45 AM	#5
Aries New Member Join Date: Mar 2002 Location: Hong Kong Posts: 11	I know how to unlock the cells. But what I want to do is: I have a rectangular range of cells unlocked. Users can input data into the cells. But if they mistakenly input data into wrong cell (say the next column, which is also an unlocked cell), they tries to move the cells to the correct address. I want to prohibit these moves.

Mar 20th, 2002, 03:47 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	The formala reference will only change if they "Cut" the cell. This is by design if you prevent this from happening the consqueces may be worse! However if this is what you want there is one non VBA approach I can think of. This is to enclose all cell references in your formulas with the INDIRECT function =INDIRECT("E1")