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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,705
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,101,845
Messages
5,483,271
Members
407,387
Latest member
ajay1303

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top