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.
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.
How to Enable or Disable Cell Drag and Drop Option in Excel 2003
Follow the below Steps:
Step 1. Go to Tools –> Options
Step 2. Select the Edit Tab. Here you have that highlighted Checkbox to enable and disable drag and drop in Excel 2003.
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.
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:
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