Excel 2010: Same VBA code but different forms controls

DavidRoger

Board Regular
Joined
Oct 2, 2011
Messages
135
Hi all,

I have several listboxes and spin buttons. They all have same code.

Here is the code I use for listbox 1 and apply to all other:

Code:
Private Sub ListBox1_Click()

    Range("A1").Value = vbNullString
       
End Sub

Here is my question. Can all the listboxes and spin buttons be combined and just using one code instead of reply them all.

I have tried something like below and similar but code run into debug

Code:
Private Sub ListBox1+ListBox2+ListBox3+Spinner1+Spinner2+Spinner3_Click()

    Range("A1").Value = vbNullString
       
End Sub

Hope someone can show me some example.

Thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,715
No, not like that, there are a couple of things that you can do - the easiest would be to have a sub that does all your stuff and then just call it from each control - though you seem to have only a single line of code anyway so that isn't really going to save you anything. Another way would be to loop over all the controls and create an new instance of a single Events Class for each control which would contain the code, but again, this would probably result in more code and is much more complex than the method you are currently using.
 

DavidRoger

Board Regular
Joined
Oct 2, 2011
Messages
135
No, not like that, there are a couple of things that you can do - the easiest would be to have a sub that does all your stuff and then just call it from each control - though you seem to have only a single line of code anyway so that isn't really going to save you anything. Another way would be to loop over all the controls and create an new instance of a single Events Class for each control which would contain the code, but again, this would probably result in more code and is much more complex than the method you are currently using.

Thanks Kyle for the explanation. Good to know VBA better.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,148
Messages
5,657,104
Members
418,355
Latest member
michaelirl

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