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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,706
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,102,362
Messages
5,486,398
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top