Excel 2010: Same VBA code but different forms controls

[DavidRoger]

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:

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

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

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

Hope someone can show me some example.

Thanks.

 

[Kyle123]

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]

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.