VBA & Macro Problems

How to Disable the Excel Fill Handle with VBA

Tauseeq MagsiTauseeq Magsi Nov 2, 2026 869 views

Question details

The user wants to programmatically disable the Excel fill handle to prevent users from dragging and dropping cells.

How to Disable the Excel Fill Handle Using VBA
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor in Excel.

2
Access the ThisWorkbook Module

In the Project Explorer pane on the left side, double-click on 'ThisWorkbook' to open the code window specifically for the active workbook.

3
Add the Workbook_Open Code

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.

4
Add the Workbook_BeforeClose Code

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 Open and Close Events to Manage the Fill Handle
Handling Canceled Close Operations: If a user is prompted to save changes upon closing and clicks 'Cancel', the BeforeClose event has already run. This means the fill handle will be restored even though the workbook remains open.
Advanced Spreadsheet Management

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. 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsb document.
  2. 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on the 'VBA Editor' icon.
  3. 3. Insert your VBA Code: Locate 'ThisWorkbook' in the Project Explorer, paste your Application.CellDragAndDrop code, and run your macros seamlessly.
Fully compatible with Microsoft Excel VBA scripts and application-level settingsFamiliar developer environment and macro editorFree, lightweight, and fast spreadsheet solution
microsoft office alternative - wps office

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.