how to lock excel from dragging?

babaso_tawase

New Member
Joined
Feb 5, 2017
Messages
46
how to lock excel from dragging in unlocked cell in locked excel sheet?
A
B

<tbody>
</tbody>
In Column A is unlocked and in B locked Cell, if dragged unlocked cells then formula in Column B changes (as formula depends on cell value in column A) to no of cells that dragged.
I want lock dragging function in excel , is it possible?
Thanks.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Have you turned protection on ?

Locking / unlocking cells has no effect until worksheet protection is turned on.
 

babaso_tawase

New Member
Joined
Feb 5, 2017
Messages
46
Yes, sheet is protected using password.
In locked cell , protection is on and in unlocked cell protection is off.
Thanks.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
No, that's not how it works, protection is either off for the whole sheet, or on for the whole sheet.

You can't have protection ON for one cell or group of cells, and OFF for another cell or group of cells on the same sheet.

Individual cells can be locked or unlocked.
But this makes no difference if the worksheet is not protected - you will be able to make changes to any cell.

But if you turn protection on, you will then only be able to make changes to cells that are UNlocked.
 

babaso_tawase

New Member
Joined
Feb 5, 2017
Messages
46

ADVERTISEMENT

No, that's not how it works, protection is either off for the whole sheet, or on for the whole sheet.

You can't have protection ON for one cell or group of cells, and OFF for another cell or group of cells on the same sheet.

Individual cells can be locked or unlocked.
But this makes no difference if the worksheet is not protected - you will be able to make changes to any cell.

But if you turn protection on, you will then only be able to make changes to cells that are UNlocked.


Actually unlocked cells for enter raw data to user and locked cells contains formulas to process data.

So we can't lock whole sheet.
and it is possible to lock or unlock cell or group of cells by using format cells option, in that protection tab by selecting lock or unlock option.then protecting sheet using password.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I'm having difficulty understanding what you are saying, but I'll try to explain again.

Individual cells (and groups of cells) can be locked, or unlocked.
The default setting on a new worksheet is normally locked.
You change the setting in the Format Cells dialog box, on the Protection tab.

On the Protection tab in the Format Cells dialog box, it tells you that
Locking cells . . . has no effect until you protect the worksheet . . .

So if you want some cells to be locked and others to be unlocked, and for the locked status to actually take effect . . .

1) Make sure that the cells you want to be locked, are actually locked, on the Format Cells dialog box
2) Make sure that the cellls you want to be unlocked, are actually unlocked, on the Format Cells dialog box
3) Turn worksheet protection ON (and you can set a password for this if you like, but it's not compulsory).

By the way, in my opinion, there's usually no need to quote entire posts on this board, it just makes the whole thread longer than it needs to be.
 

babaso_tawase

New Member
Joined
Feb 5, 2017
Messages
46

ADVERTISEMENT

Yes Sir, all done as mentioned above message. I actually want lock dragging function on unlocked cells.
Thanks
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,115
Office Version
  1. 2016
Platform
  1. Windows
Is it possible for excel 2003?
Yes.
TOOLS, OPTIONS, EDIT, checkbox ALLOW DRAG AND DROP

Note that this is an application level setting so if the workbook is opened by a user on a different machine you may need to set this with VBA upon opening/Activating the workbook as follows : Application.CellDragAndDrop = False
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,140
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top