Identify clicked ActiveX Command Button

fr_Mu

New Member
Joined
Feb 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all. Hoping someone can help. I've put together code to transfer data between sheets. The applicable data from sheet1 will transfer and overwrite the existing data on sheet2 (how I want it). Each line of data on sheet1 has an ActiveX Command Button next to it to trigger the transfer. I want each button to run the same macro, but the Command Button name to change based on the button that was clicked. The part I can't break is changing the CommandButton"#", based on a changing variable. New rows with buttons can be added as needed (generated automatically through vba) and i need those newly generated buttons to operate the same way. They need to operate independently, so a loop is not what I'm looking for. Thanks in advance all. Been pulling my hair out!

VBA Code:
Sub getRow()

Dim CommandButton As Object
Dim r_no As Integer
Set Obj = ActiveSheet.CommandButton1
    With Obj.TopLeftCell
    r_no = .Row

    Sheets("CARDS").Cells(2, 3).Value = Cells(r_no, 6).Value
    Sheets("CARDS").Cells(20, 2).Value = Cells(r_no, 22).Value
    Sheets("CARDS").Cells(21, 2).Value = Cells(r_no + 1, 22).Value
    Sheets("CARDS").Cells(22, 2).Value = Cells(r_no + 2, 22).Value
    Sheets("CARDS").Cells(20, 3).Value = Cells(r_no, 23).Value
    Sheets("CARDS").Cells(21, 3).Value = Cells(rno + 1, 23).Value
    Sheets("CARDS").Cells(22, 3).Value = Cells(r_no + 2, 23).Value

End With

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
928
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
a VBA help must see : Caller …​
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,327
Office Version
  1. 2010
Platform
  1. Windows
so you need a new button for each row in your data. is it not possible to select a cell in the row and then click the same button over and over? what your are asking for is possible but a bit complex, requiring a class module to do the work. using 1 button would be a lot simpler to code, but might not suit your design
 

fr_Mu

New Member
Joined
Feb 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thanks diddi. I've tried adding a class module, but wasn't very successful. I create the rows with a module. It generates a range of 4 rows including a command button. Each command button needs to refer to that range of cells when clicked. Other modules are linked to each new range for various automated tasks. I need to identify the command button selected to get the info from the range (rather row(s)) it's part of (sits over). I then need to create an additional command button to run through each one. That's easy, but first I need them to be able to run independently, processed through that one module.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,189
Members
418,288
Latest member
reba557

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top