Excel Option Button refuses to work

[wisgro]

Read so much option button help today that doesn't work I'm about to chuck the project. All I want to do is create a group of 3 option buttons in an excel worksheet for a user to select one of two farm programs or none. (CIG or MRP or None) When a user makes a selection either CIG or MRP or None appears in a designated cell. I even used code in the VB editor help but it only shows how to operate one button (which is absurd.) I found an example using 2 buttons, but that wouldn't work either. Found a tutorial using 3 buttons and that wouldn't work. Is this a conspiracy? Every time I try to find help to do something simple in excel lately I hit a brick wall. All I find in excel help is complex examples. Is simplicity dead? Don't get me started on macros.

[mudraker]

You have not explained what you want to occur when a user selects an option

[shg]

Create three option buttons from the Forms toolbar. Right-click any of them, select Format Control, and assign a cell link, say A1 (they all link to the same cell).

In another cell, =CHOOSE(A1, "CIG", "MRP", "None")

Simplicity lives.

[wisgro]

Thanks a million. That's excellent. But excel exacts its pound of flesh: The frame and option buttons are transparent and move independently of each other. I've tried all I know and so far no luck. It looks weird with the gridlines showing through.

[shg]

You're not trying hard.

The frame and option buttons are transparent ...

Format Control, Color and Lines, pick a fill color you like.

... and move independently of each other

Select the Group Box and each of the option buttons while holding the Ctrl key to multi-select. Right click, Grouping, Group.

[wisgro]

That's the first thing I tried. I selected the group box then clicked Format>Control. A Format Control group of tabs opens. Tabs are Size, Protection, Properties, Web, Control. None of these tabs have the color or other common format options that are usually available in properties. I then selected each button and got the same results when I click Format Control. I tried everything in my limited bag of tricks to get to a properties menu without success. I'm using excel 2002 (10.6841.6839) SP3. I even promised excel some hagen daas sprinkled with crunchy numbers. Apparently excel doesn't bribe easily, gives up its secrets begrudgingly and loves to confounds brilliant minds.

[wisgro]

I just discovered that the Format Control only offers Lines and Colors when the button is first placed onto the worksheet and before any type of code has been inserted. After code has been inserted a different set of options become available in Format Control, none of which offer Lines and Color.

[shg]

I just discovered that the Format Control only offers Lines and Colors when the button is first placed onto the worksheet and before any type of code has been inserted.

Interesting. Not so with Excel 2003.

Apparently Excel doesn't bribe easily, gives up its secrets begrudgingly and loves to confounds brilliant minds.

“Nothing in this world can take the place of persistence. Talent will not; nothing is more common than unsuccessful people with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. The slogan "press on" has solved and always will solve the problems of the human race”

Calvin Coolidge, 1872

[wisgro]

After your instructions I tried an experiment: created a new frame. The Line and Color tab was still not available in the Form Control. Created a new option button and the L & C tab was available for buttons. Deleted the 3 existing buttons and created 3 new buttons and placed them inside the frame. The L & C tab was still available to the buttons. Conclusion for excel 2002: L & C is never available for frames, aka group boxes. If at first L & C is not available for option buttons delete them, create new buttons and start again until the L & C tab is part of the form control for buttons. Excel glitch? Before discovering this I found a cheap way around the transparent frame problem: I colored the cells behind it. Of course lines were not available for the option buttons. Thanks for your help. Have a great weekend.

[shg]

You could instead use ActiveX controls from the Control Toolbox, which offer more options for formatting, at the expense of requiring some VBA to automate.

Or, if your requirement is a simple as a few option buttons, you could use Marlett checkboxes, implement your own groupings, and have all of the cell formatting options available.

Excel offers many ways to skin a cat, even if you can't find the exact Cat Imperial recipe you're looking for.

Have a great weekend.

Thank you, and you as well.