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