Assigning the same sub to multiple controls

Question

I have an Excel worksheet that has multiple sections for collecting information from several different sources.

Each section has several rows with an option to add more rows, if necessary, using a command button to run a sub that will add the requested number of rows. 

One section deals with tax information, and includes a combo box (cbo) on each row to define tax type and jurisdiction for a defined piece of equipment to determine into which general ledger account to post the tax amount. 

The combo boxes are all linked to the same input list, and their linked cell is the row where the cbo is located (the cbo_Tax33 box is in row 33, overlaid on cell K33, and it's linked cell is K33 All relevant cbo are in column K). 

When the user selects the appropriate jurisdiction from the cbo list, it calls a function to return the appropriate GL account that goes into the relevant column (in the previous example, the account number would be returned to A33)

The problem I'm facing, is that if I add 50 rows, am I going to have 50 code subs that read:

Private Sub cbo_Tax33_Change()

Cells(33,"A") = fn_Account(33)

End Sub

where the function fn_Account Row parameter gives row of activecell, which is used to get other information needed to determine the account number (State, Zip Code, TaxType, Local vs County vs State, etc..)

Question:  Is it not possible to assign the same sub :

Private Sub cbos_Changed()

ro = activecell.row

Cells(ro,"A") = fn_Account(ro)

End Sub

so I don't need to create a seperate one for each cbo on each added row?

TIA...

Answer 1

You can do something similar if you have ActiveX controls.

In that case you have to create class module with an event variable and the event routine.

Then you have to create an instance of that class for each of your controls, assign the control to the event variable in the class and finally store the instance into a collection.

Give me a sign if you need an example.

Andreas.

Answer 2

If you use Forms Controls instead of ActiveX controls then you can assign the same sub to multiple controls. If required, you can use Application.Caller to identify the control that called the sub.

The Subs for Forms controls go into standard modules. The sub name can be any name. You Right click the control and select Assign macro. You can select any valid sub from the list or if you select New then a macro name will be automatically created. You can then create other controls and using Assign Macro, the same sub can be assigned to them.