Running Macro when option button selected

[jeffcp66]

I have three option buttons to be selected. When one is selected, you can then "activate" your choice by using a dropdown menu. But just selecting an option button will not activate the desired change, so I wish to change the color of the cells behind the button from green to yellow when the button is selected, to indicate that your selection has not been activated. Then, when you do activate with the dropdown menu, that macro changes the cells back to green.

I've gotten the cells to turn yellow by using a Calculate event in the sheet code for the reference cell connected to the Option Buttons. But my problem is that ANY change event on the sheet will cause the cells behind the selected button to turn yellow. How do I make it so ONLY a change to the reference cell (which is L7) will trigger the color change?

Here is the code:

Please Login or Register  to view this content.

[Eastw00d]

Maybe I do not understand the problem completely, but what if you try this:

Please Login or Register  to view this content.

Cheers
Erwin

[torachan]

Use the WorkSheet_Change event ???

[jeffcp66]

No, the change event does not work, I believe because I'm not manually changing L7... the Option Button selection is changing it. I've researched online for answers for days now. Most advice is to use "calculate" to trigger a macro if a cell is changed via a formula. This isn't exactly a formula, but in any case, the cell's value is changed by something other than manual entry, so Change Event does not work.

[torachan]

Why not use one of your other events (option buttons) to trigger colour change, why does it have to be specifically cell value change.

[jeffcp66]

Hello, torachan, I tried your code but it doesn't work. I changed it to "OptionButton6", because that's what appears in the name box for one of the buttons. But when I click it, the cells do not change color. Your code is sheet code, correct?

(note, I don't "need" it to be specifically cell value change... I'm just trying to figure out anyway to make it work. Thanks for your help)

[jeffcp66]

torachan, when I press "play" in VBA your code DOES change the cells color, but it is not being triggered by clicking the button. I noticed on your sheet the button's name is OptionButton1 with no space before the button. On my sheet it is OptionButton 6, with a space before the six. I cannot enter a space into the code without getting an error. I manually changed the button's name to "Jill", and change the code to "Jill_Click", but that didn't work either.

[jeffcp66]

Finally figured it out. Just needed to write a separate macro, not a sheet event, then assign macro to each option button with right click:

Please Login or Register  to view this content.

[torachan]

Sorry been away from my computer for a few hours.
Glad you have it sorted now.
If you need any more help upload your workbook it is a lot easier to work with the total package.