Hi Friends,

Before I go and explain you how to enable or disable drag and drop option in Excel, let me explain you what is Drag and Drop Option in Excel.

What is Drag and Drop Option in Excel ?

When you select a Cell in Excel and roll your mouse to the Right-Bottom Corner, one Plus Sign ( + ) will be appearing. On Clicking on that you can drag towards Column or Row in any Direction like Left, Right, Above or down. After dragging you can copy the Cells data in all the Cells till you dragged.

draganddrop
While working on Excel, you suddenly find that you are unable to Drag and Drop a Cell. You DO NOT see the little dot at the Right-Bottom corner of your selected cell or range, clicking on which you see a plus sign to hold and drag it in all directions (Up, Down, Left and Right). Refer the above picture (gif)

Huuuhh… without this working with Excel seems difficult because you are not able to drag a Series or Formula and many more things. Then you start looking for option to make this function enabled. Here is the step-by-step solution for that. It differs in Excel 2003 and 2007 or 2010. I will show you, how to Enable/Disable this property in Excel 2003 and 2007. In Excel 2007 and 2010, it is same.

Note: Cell drag and drop option is applicable for Excel as an application. This means, if you disable it in any of your Excel workbook, it will get disabled for Excel application in your computer. It means any workbook you open, you will find it disabled. Similarly, enabling it in any workbook will enable it for all the workbooks you open thereafter

How to Enable or Disable Cell Drag and Drop Option in Excel 2003

Follow the below Steps:
Step 1. Go to Tools –> Options

Drag and Drop in Excel 2003

Drag and Drop in Excel 2003

Step 2. Select the Edit Tab. Here you have that highlighted Checkbox to enable and disable drag and drop in Excel 2003.

Drag and Drop in Excel 2003 - 2

Drag and Drop in Excel 2003 – 2

How to Enable or Disable Cell Drag and Drop Option in Excel 2007

Follow the below Steps:
Step 1. Click on the Excel Button at the Top Left Corner of Excel 2007 or 2010.

Drag and Drop in Excel 2007 -1

Drag and Drop in Excel 2007 -1

Step 2. Go to Advanced Tab
Step 3. Here in right hand side, you find the Checkbox to enable and disable this property as shown below in the picture:

Drag and Drop in Excel 2007 -2

Drag and Drop in Excel 2007 -2

Excel VBA code to Enable or Disable Cell Drag and Drop Option

How can I not tell you how to do this in Excel VBA !! Ofcourse, I will tell you the VBA code which can be used to enable or disable the Cell Drag and Drop” option in Excel.
As I have highlighted above, this option is applicable for Excel as an application and not specific to a workbook or worksheet. Hence be careful while enabling and disabling it.


Sub DragAndDropControl()

    'To disable cell drag and drop option in Excel
    Application.CellDragAndDrop = False
    
    'To enable cell drag and drop option in Excel
    Application.CellDragAndDrop = True

End Sub
From above code also you can see that this setting belongs to Application and not to specific to a workbook or worksheet

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!