VBA & Macro Problems

Use One Excel VBA Macro for Multiple Form Buttons

Huda QurayshiHuda Qurayshi Nov 22, 2026 868 views

Question details

Use one VBA macro for multiple worksheet buttons so each button copies data from its own row.

Use One Excel VBA Macro for Multiple Form Buttons
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains several buttons, each associated with a different row of data.
Observed behavior
The shared macro should identify the clicked button and use its position to select the corresponding row data.
Before you start

Make sure the buttons are worksheet shapes or Form Control buttons, not ActiveX controls or UserForm buttons. Enable the Developer tab if needed and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Identify the clicked button with Application.Caller

Assign the same macro to each worksheet button and use its top-left cell to find the data to copy.

Application.Caller returns the name of the clicked shape or Form Control button. TopLeftCell identifies the cell beneath its upper-left corner, and Offset selects a cell relative to that position.

The example copies the cell immediately to the right of each button's top-left cell to the next row in column A on Sheet2. It assumes Sheet2 has a header in A1.

1
Prepare the destination

Create or rename a worksheet Sheet2 and enter a header in cell A1. Place each source button so its upper-left corner is in the intended row.

2
Add the shared macro

Open Developer > Visual Basic, choose Insert > Module, and paste this code: Sub CopyFromButtonRow() Dim rng As Range Dim dest As Worksheet Set rng = ActiveSheet.Shapes(Application.Caller).TopLeftCell Set dest = ThisWorkbook.Worksheets("Sheet2") rng.Offset(0, 1).Copy Destination:=dest.Cells(dest.Rows.Count, "A").End(xlUp).Offset(1, 0) End Sub

3
Assign the macro

Right-click each worksheet shape or Form Control button, select Assign Macro, choose CopyFromButtonRow, and click OK.

4
Test each button

Click a button on the worksheet and check column A on Sheet2. Adjust rng.Offset(0, 1) in the VBA editor if the source data is in a different position relative to the button.

Identify the clicked button with Application.Caller
If the shape name is not found: Run the macro by clicking an assigned worksheet button rather than from the VBA editor. Check that the button is on the active worksheet and that it is a shape or Form Control; ActiveX and UserForm buttons need a different event-based approach.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheet files. Its Microsoft Office format compatibility helps you move existing files into WPS; test macro-dependent workbooks before relying on the same automation.

Open and edit Microsoft Excel XLSX and XLS filesReview spreadsheet data and button layouts in a familiar interfaceUse a free, lightweight office suite for everyday documentsMove compatible Office files into WPS without recreating them
microsoft office alternative - wps office

Frequently Asked Questions

How does the macro know which button was clicked?

When you click an assigned worksheet shape or Form Control button, Application.Caller returns its name. ActiveSheet.Shapes(Application.Caller).TopLeftCell then returns the cell beneath the button's upper-left corner.

Why does Excel say the item with the specified name was not found?

The caller may not be a shape on the active worksheet. Click the assigned worksheet button to run the macro, check that the button is on the active sheet, and confirm it is a shape or Form Control rather than an ActiveX control.

Can I use this exact macro with an ActiveX or UserForm button?

No. The Application.Caller shape lookup shown here is intended for worksheet shapes and Form Control buttons. ActiveX and UserForm buttons use their own event procedures and need a different way to identify the control and source data.

How do I copy a different cell from the button's row?

In Developer > Visual Basic, change rng.Offset(0, 1) to the offset you need. For example, rng.Offset(0, 2) selects the cell two columns to the right of the button's top-left cell.

Why does a button copy data from the wrong row?

TopLeftCell uses the cell beneath the button's upper-left corner. Move or resize the button so that corner sits in the intended row, then click the button again.