How to Disable the Excel Fill Handle with VBA
Question details
The user wants to programmatically disable the Excel fill handle to prevent users from dragging and dropping cells.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a protected workbook where users might accidentally overwrite formulas or alter formatting by dragging cells using the fill handle.
- Observed behavior
- Even when the workbook is protected, the fill handle remains active, allowing users to drag and drop cells and potentially corrupt data or formatting structures.
Before modifying application-level properties like cell drag-and-drop with VBA, save a backup of your workbook and note that these changes can affect all open workbooks if not carefully managed.
Use Open and Close Events to Manage the Fill Handle
Save the user's original drag-and-drop setting when the workbook opens, disable the feature, and restore the original setting when the workbook closes.
Because Application.CellDragAndDrop is an application-level setting, disabling it will affect the entire Excel instance. Storing the original state in a Public variable ensures it is correctly restored when the user finishes working.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor in Excel.
In the Project Explorer pane on the left side, double-click on 'ThisWorkbook' to open the code window specifically for the active workbook.
Declare a Public Boolean variable at the top (e.g., Public bolDragAndDrop As Boolean). Create a Private Sub Workbook_Open() event that saves the current Application.CellDragAndDrop value to the variable, then sets Application.CellDragAndDrop = False.
Create a Private Sub Workbook_BeforeClose(Cancel As Boolean) event. Inside this subroutine, set Application.CellDragAndDrop back to your saved bolDragAndDrop variable to restore the setting upon exiting.

Use Activate and Deactivate Events
Disable the fill handle only when the target workbook is actively in focus, and restore it when switching to another workbook.
Execute Macros and Manage Settings in WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to easily run and edit scripts like disabling the fill handle. It provides a familiar developer environment that is highly compatible with Microsoft Excel VBA.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsb document.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on the 'VBA Editor' icon.
- 3. Insert your VBA Code: Locate 'ThisWorkbook' in the Project Explorer, paste your Application.CellDragAndDrop code, and run your macros seamlessly.

Frequently Asked Questions
Why does disabling the fill handle affect my other Excel files?
The CellDragAndDrop property is an application-level setting in Excel. When you change it using VBA, it applies to the entire Excel application, not just the active workbook. This is why you must use VBA events to restore the setting when switching workbooks or closing the file.
Can I disable the fill handle without using VBA?
Yes. You can manually disable it by navigating to File > Options > Advanced, and unchecking 'Enable fill handle and cell drag-and-drop' under the Editing options. However, this must be done manually and affects the user's entire Excel application until re-checked.
What happens if VBA code execution is interrupted before restoring the setting?
If the macro crashes or execution is halted before the BeforeClose or Deactivate events run, the fill handle will remain disabled in Excel. The user will need to manually re-enable it via Excel Options > Advanced.




