How to make a button VBA code reference other VBA code subroutines???

G

gunman

Hey you Excel geniuses out there. I have a little problem that is
stumping me. I have a small excel program that uses 6 inputs that the
user changes, a button with VBA code and an output. When the user makes
their changes, they press the button and the new value is calculated
based on the VBA code and is spit out in the output field. I have about
10 copies of this button with 10 different output fields. This is simply
so the user can compare conditions, setups, try different combinations,
etc... without having to lose the previous output every time. HERE IS
THE PROBLEM - I am trying to create an "Update All" button. This button
would theoretically perform the function of all 10 other buttons
simultaneously and update everything. However, I don't know of any way
to make this one button reference the subroutines of the other 10.
Right now, I have to copy and paste all 10 copies of the code into the
code for he Update All button. When I do this, Excel says that there is
too much code and won't perform the function.
DOES ANYONE KNOW HOW TO SIMPLY REFERENCE THE SUBROUTINES FROM THE OTHER
BUTTONS RATHER THAN COPYING AND PASTING THE CODE?? This would greatly
help. Thank You

Gunman
 
B

bill k

Sub doitall()
routine1
routine2
etc.....
End Sub

where routine1 is the name of the sub you assigned to button1
no brackets just the name
 
G

Guest

Create 10 sub procedures in a module and have each button call one of the sub
procedures. Then you can call the 10 sub procedures in the Update All button.
 
R

R. Choate

The problem you described is a problem only because you have all of that code in the click event for each of those seperate
controls. I'm afraid you would need to copy the code to a standard module and seperate it into several different subs (equal to the
number of buttons you currently have code for). This way, you could actually just make the click event for each button reference the
macro in the module instead of having the code right there in the click event. It is only tricky if the code needs to manipulate a
form or other controls in addition to the tasks you described. Anyway, once this transfer of code to the standard module and
seperate subs is complete, running all of the code in sequence would be cake.

HTH
--
RMC,CPA



Hey you Excel geniuses out there. I have a little problem that is
stumping me. I have a small excel program that uses 6 inputs that the
user changes, a button with VBA code and an output. When the user makes
their changes, they press the button and the new value is calculated
based on the VBA code and is spit out in the output field. I have about
10 copies of this button with 10 different output fields. This is simply
so the user can compare conditions, setups, try different combinations,
etc... without having to lose the previous output every time. HERE IS
THE PROBLEM - I am trying to create an "Update All" button. This button
would theoretically perform the function of all 10 other buttons
simultaneously and update everything. However, I don't know of any way
to make this one button reference the subroutines of the other 10.
Right now, I have to copy and paste all 10 copies of the code into the
code for he Update All button. When I do this, Excel says that there is
too much code and won't perform the function.
DOES ANYONE KNOW HOW TO SIMPLY REFERENCE THE SUBROUTINES FROM THE OTHER
BUTTONS RATHER THAN COPYING AND PASTING THE CODE?? This would greatly
help. Thank You

Gunman
 
R

R. Choate

The reason the poster is having trouble is that he did not assign routines to his buttons. The code he wants to run is in the
button_click event on his form. Your solution will be fine after he moves all of that code that he doesn't want to copy into
seperate subroutines. Until then, he cannot solve his problem. Your solution would be obvious if he had already assigned routines to
his click events instead of putting his code there. If he had known enough about VBA to have his code in seperate subroutines and
assigning them, assuming that his code doesn't also manipulate the form and its controls, he would already know enough not to need
our help.
--
RMC,CPA



Sub doitall()
routine1
routine2
etc.....
End Sub

where routine1 is the name of the sub you assigned to button1
no brackets just the name
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top