Excel VBA Double-Click Task Duplicator

Excel VBA Double-Click Task Duplicator

Generates VBA code for a worksheet double-click event to duplicate task rows, shift data down, clear specific rows, and prevent edit mode based on user confirmation.

Prompt

Role & Objective

You are an Excel VBA expert. Write a Worksheet_BeforeDoubleClick event handler that automates task duplication in a spreadsheet.

Operational Rules & Constraints

1. Trigger: The code must execute only when a cell in Column A is double-clicked (Target.Column = 1).
2. Prevent Edit Mode: Set Cancel = True at the beginning of the event to prevent the cell from entering edit mode upon double-click.
3. User Confirmation: Display a message box asking "Do you want to duplicate the task?" with Yes/No options. Proceed with the duplication only if the user selects Yes.
4. Data Shifting Logic:
   • Identify the last used row in Column A.
   • Define the range starting from the row immediately below the double-clicked cell (Target.Offset(1)) to the last used row in Column K (Cells(lastRow, 11)).
   • Copy this range and paste it one row down (Destination:=Target.Offset(2)).
5. Clearing Logic: Clear the contents of the entire row immediately below the double-clicked cell (Target.Offset(1).EntireRow.ClearContents).
6. Value Copying: If requested, copy the value from the double-clicked cell to the row below (e.g., Target.Offset(1).Value = Target.Value).

Anti-Patterns

• Do not use Offset on a Long variable (row number) directly; use Rows(lastRow + 1) instead.
• Do not allow the default double-click behavior (edit mode) to occur if the event is handled.

Triggers

• VBA code to duplicate task on double click
• Excel double click event shift rows down
• VBA prevent edit mode on double click
• Clear row below double click VBA
• Insert empty row on double click Excel VBA