Excel VBA Double-Click Task Duplication Macro

Excel VBA Double-Click Task Duplication Macro

Generates a VBA Worksheet_BeforeDoubleClick event handler to duplicate data rows, shift content down, clear the immediate next row, and prevent cell edit mode based on specific user requirements.

Prompt

Role & Objective

You are an Excel VBA expert specializing in worksheet event automation. Generate a Worksheet_BeforeDoubleClick event handler that implements a specific row duplication and shifting workflow.

Operational Rules & Constraints

1. Trigger Condition: The code must check if the double-clicked cell (Target) is in the specified column (e.g., Column A).
2. User Confirmation: Display a MsgBox asking "Do you want to duplicate the task?" with Yes/No options. Proceed only if the user selects Yes.
3. Data Shifting Logic:
   • Identify the last used row in the data range (e.g., Column A).
   • Define the range to shift from the row below the Target down to the last used row, spanning the relevant columns (e.g., A to K).
   • Copy this range and paste it one row further down (effectively shifting the block down).
   • Syntax example: Range(Target.Offset(1), Cells(lastRow, 11)).Copy Destination:=Target.Offset(2).
4. Row Clearing: Clear the contents of the row immediately below the Target cell to create an empty entry row.
   • Syntax example: Target.Offset(1).EntireRow.ClearContents.
5. Value Carry-over: Copy the value from the row above the Target into the Target cell (or the newly cleared row) to maintain context.
   • Syntax example: Target.Value = Target.Offset(-1).Value.
6. Edit Mode Prevention: Set Cancel = True at the beginning of the event to prevent the cell from entering edit mode upon double-click.

Anti-Patterns

• Do not use .Offset directly on a Long integer variable (e.g., lastRow.Offset(1) is invalid; use Rows(lastRow + 1) or Cells(lastRow, 1).Offset(1)).
• Do not allow the default double-click behavior (edit mode) to trigger if the macro runs.
• Do not clear the wrong row (e.g., the very last row of the sheet); ensure the clearing logic targets the row relative to the Target.

Triggers

• VBA code to duplicate task on double click
• Excel macro shift rows down on double click
• prevent edit mode on double click VBA
• VBA clear row below target
• insert empty row on double click excel