Running the code in an option button from a seperate normal button click

[camikhazi]

Hi,

I have a series of normal buttons and option buttons in my excel spreadsheet, i would like to edit my normal button macro so that when the button is clicked it will 1) figure out which option button is selected at the time and then 2) run the macro associated with that option button.

I'm sure there must be a simple way to do this???

Thanks,
Cam

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[camikhazi]

Runtime Error 424: Object Required....

what does that mean?

[AlphaFrog]

Let's take a step back.

First, there are two types of controls you can have on a worksheet; ActiveX-type and Form-type.

Overview Form controls and ActiveX controls on a worksheet

What type of Command Button and Option Button controls are you using; ActiveX-type or Form-type?

What are the names of the controls?

What are the names of the macros you want to call for each Option Button?

[camikhazi]

Sorry for the slow reply,

Okay so i am using Form-Type Command and Option Buttons.

I have 2 command buttons, one with the macro "Plus" and one with the macro "Minus" that add and subtract respectively a value of 1 from the cell they are in.

I have 6 option buttons attached to 6 macros called "NoRake", "AveRake", "RatioedRake", "NormalRake", "TurboRake" and "HyperRake"

Each of these macros attached to the option buttons perform slightly different calculations on a series of cells.

That all works fine

However, when i adapt the data (using the command buttons), these calculations are not recalculated until i reclick the option button.

What i would like, is for this recalculation to be done as soon as i adapt the data. Ie. When i click on the command button.

Does that make it a bit clearer?

Cam

[AlphaFrog]

Add this to the bottom of your Plus and Minus macros. Change the names of the "Option Buttons" to suit. Don't use the option button captions. Use thier names as seen in the name box to the left of the formula bar.

Please Login or Register  to view this content.

[mikerickson]

Something like

Please Login or Register  to view this content.

[camikhazi]

Amazing AlphaFrog thank you so much!

[AlphaFrog]

Amazing AlphaFrog thank you so much!

You're welcome. Thanks for the feedback.

[YasserKhalil]

Thanks a lot Mr. AlphaFrog for this great code
Mr. Mike I am trying to test your code but I encountered excel crash
Here's my attempt to test your code. Can you tell me what's wrong with it?

[AlphaFrog]

Thanks a lot Mr. AlphaFrog for this great code
Mr. Mike I am trying to test your code but I encountered excel crash
Here's my attempt to test your code. Can you tell me what's wrong with it?

Assign your TestA, TestB, and TestC macros to each option button and not your Mike macro.

[YasserKhalil]

Thanks a lot Mr. AlphaFrog for reply
So what is the benfit of Mr. Mike's code?
I thought it would be deal with any option button according to its caption...

As for your code I assigned one sub (named it AlphaFrog) to all option buttons and it works well and call proper sub ..

[AlphaFrog]

Thanks a lot Mr. AlphaFrog for reply
So what is the benfit of Mr. Mike's code?

I don't know. You would have to ask Mike.

I suppose Mike's is not dependent on hard-coding the option button names to their macros. If you wanted to change the assigned macro for a particular option button, you wouldn't have to change the code.

I would suggest it's a little more concise to use an OptionButton object and the OptionButtons collection instead of looping through the Shapes collection. This does the same thing.

Please Login or Register  to view this content.

[YasserKhalil]

But the last code you provided cause excel crash too ..

[AlphaFrog]

But the last code you provided cause excel crash too ..

The last code I provided does the exact same thing as Mike's code. I was simply showing a shorter way to do the exact same thing. So if you didn't change the assigned macros as I stated in post #11, then both Mike's and my shorter version of Mike's will throw an error.

[mikerickson]

I read your problem as that you have existing Option buttons, each of which has an associated Macro. And you wanted a Macro that you could assign to a separate Command button. The Option Buttons should not call the code I posted.
See attached

[YasserKhalil]

Thanks a lot Mr. Mike
So I have to assign a macro for each option button first ..

Is there a way to assign all option buttons to one sub and call this sub based on the caption of the option button..?

[mikerickson]

Sure,
Something like this maybe. (note that this must be called by activating the control, pressing F5 while testing will cause an error)

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Mike for this great and wonderful help
I tested the following line and everything is ok

Please Login or Register  to view this content.