Excel VBA- Multiple Command Buttons- Single Macro

[vjmehra]

I have some code that loops through a series of rows, the data comes from a MySQL database and when a query button is clicked the code fires and a command button is created on each row.

The name of the command button is assigned by an ID field, which is a column from the database.

So far so good, this all functions correctly.

However, this is where I get stuck as I now have a series of buttons and depending on which button was clicked I need a different piece of code triggered.

I can't simply assign some code to each button, as these buttons get deleted and re-added depending on the data pulled back from the database.

So for example, on a query button click, a series of buttons with ID's 99, 101, 103 are created, then when the query button is clicked again, it deletes all rows and say pulls back ID's 88 and 113.

So, what I'm looking for is a piece of code that can tell which button was clicked, as I can't use the standard Private Sub btnsearch_Click() as the name of the buttons keeps changing.

I keep seeing code samples containing the following:

Application.Caller

However this does absolutely nothing, I suspect because it only works for buttons, not Command Buttons.

Help!

 

[JHB]

At same time as the buttons are created, are you not able to put in a function name on the click event?
Then you can create a function which tells you, which button was clicked.
See attached pic for explanation.

 

[vjmehra]

It may be possible to be fair, but in Excel if it is, I'm not sure how to do it :-(

 

[JHB]

It may be possible to be fair, but in Excel if it is, I'm not sure how to do it :-(

Is it in Excel or in MS-Access you want to know which button is clicked?

 

[vjmehra]

In Excel (the whole code and all controls are in a single Excel workbook).