如何为按钮分配VBA宏以在按钮所在行执行指定操作？

Got it, let's break down how to make this work perfectly. The key here is using Application.Caller to identify which button was clicked, so the macro only targets that button's row. Here's the step-by-step implementation:

1. The Core Macro: Move Task to History #

This single macro will handle all your buttons. It finds the button's row, cuts the row, and pastes it to the history sheet.

Sub MoveTaskToHistory()
    Dim clickedBtn As Button
    Dim targetRow As Long
    Dim historyWs As Worksheet
    
    ' Set reference to your history worksheet (make sure the name matches exactly)
    Set historyWs = ThisWorkbook.Worksheets("history")
    
    ' Get the button that triggered the macro
    Set clickedBtn = ActiveSheet.Buttons(Application.Caller)
    
    ' Get the row number where the button is located
    targetRow = clickedBtn.TopLeftCell.Row
    
    ' Cut the entire row and paste to the next empty row in history
    Rows(targetRow).Cut
    historyWs.Cells(historyWs.Rows.Count, 1).End(xlUp).Offset(1).Insert Shift:=xlDown
    
    ' Optional: Delete the now-empty row from the task sheet
    Rows(targetRow).Delete Shift:=xlUp
End Sub

How this works: #

• Application.Caller returns the name of the button that ran the macro, so we can pinpoint exactly which one was clicked.
• clickedBtn.TopLeftCell.Row gives us the row number where the button sits—this is what makes the macro target only that row.
• We paste the row to the first empty row at the bottom of the history sheet, then clean up the empty row in the task sheet.

2. Update Your "Add New Task" Macro #

When you dynamically add new rows and buttons, you need to assign the MoveTaskToHistory macro to each new button. Here's how to adjust your existing add-task code:

Sub AddNewTask()
    Dim taskWs As Worksheet
    Dim newRow As Long
    Dim newTaskBtn As Button
    
    ' Set reference to your main task worksheet (change name if needed)
    Set taskWs = ThisWorkbook.Worksheets("Tasks")
    
    ' Find the next empty row in the task sheet
    newRow = taskWs.Cells(taskWs.Rows.Count, 1).End(xlUp).Row + 1
    
    ' Populate your new task data (customize this to match your columns)
    taskWs.Cells(newRow, 1).Value = "New Task" ' Task name
    taskWs.Cells(newRow, 2).Value = Date ' Due date example
    ' Add more columns as needed...
    
    ' Add a form control button to the new row (adjust position to your desired column)
    Set newTaskBtn = taskWs.Buttons.Add( _
        Left:=taskWs.Cells(newRow, 4).Left, _
        Top:=taskWs.Cells(newRow, 4).Top, _
        Width:=taskWs.Cells(newRow, 4).Width, _
        Height:=taskWs.Cells(newRow, 4).Height)
    
    ' Set button properties
    newTaskBtn.Caption = "Mark Complete"
    newTaskBtn.Name = "TaskButton_" & newRow ' Unique name to avoid conflicts
    newTaskBtn.OnAction = "MoveTaskToHistory" ' Assign the macro to this button
End Sub

Key Notes: #

• Make sure your worksheet names ("Tasks" and "history") match exactly what's in your workbook—capitalization matters!
• Adjust the column number (in taskWs.Cells(newRow, 4)) to place the button in the right column for your layout.
• Form control buttons are used here because they're simpler to dynamically assign macros to compared to ActiveX buttons.

Troubleshooting Tips #

• If the macro can't find the history sheet, double-check the sheet name (no typos, extra spaces).
• If the button doesn't trigger the macro, ensure newTaskBtn.OnAction is set to the exact macro name ("MoveTaskToHistory"—no spaces unless your macro has spaces).
• If rows aren't deleting correctly, make sure there's no merged cells in the row (merged cells can break row operations).

内容的提问来源于stack exchange，提问作者John Farm