optionbutton takes two clicks to change value

[Will Gardner]

Hi all,

I have searched the Google archives for an answer to this problem to
no avail:

Using XL2000 SP3 on NT4 SP6 and control toolbox controls:

When I have more than 3 option buttons grouped on a user form and
their control sources assigned to cells on a worksheet it takes two
clicks to change from one option to another. On the first click, all
options go false (along with the corresponding ControlSource cell
values). A second click will set the value of the clicked option to
true (along with its ControlSource cell value). I have been able to
replicate this on a very basic form:

-Insert a userform
-Drop 4 or more option buttons onto it
-Assign all of the options buttons the same group name
-Assign each option button's ControlSource property to a different
worksheet cell

show the form and click between options.

Any insight is appreciated.

Thanks,

-Will

 

[Tom Ogilvy]

Initialize your linked source cells to False. Then show the form (or do it
in the initialize event of the form).

Once the linked source cells are all holding a boolean value, you should not
have a problem.

 

[Will Gardner]

Tom,

Thanks for the advice. Interestingly enough, while it worked just
swell in my test workbook, it did not change anything in the
application I am working on and I am not sure what the fundamental
difference between the two is. I did note that in the test workbook,
all of the values must be initialized to false in order for the
buttons to function properly. If one is initialized to true and
others to false, a double click is necessary to change the selection
the first time. Once it has changed, behavior seems normal.

In my application:

If I show the form and move it to a location so that the linked cells
on the worksheet are visible, I observe the following behavior:

When all options are false, clicking on any option will change its
value to true, run the click event code, and change the appropriate
linked cell value to true. - OK

However....

if option 1 is true and I click on option 2, the following happens:

option 1's value changes to false, the radio button black dot is
cleared, and the linked cell's value is changed to false. option 2's
value is changed to true (msgbox option2.value code in click event),
but the option 2 radio button does not get it's black dot, nor does
the linked cell value on the worksheet change. If I click again on
option 2 (or click on any other option), the appropriate values are
set and the radio buttons are filled/unfilled appropriately.

Putting a Repaint in the click events of the option buttons had no
effect. Putting a Calculate in the click events of the option buttons
effectively negated the effects of any and all clicks (no buttons nor
values would change regardless of what was clicked)

Any suggestions?

Thanks,

-Will

 

[Will Gardner]

After more searching through the Google archives, I found a workaround
to the problem. If I set Application.Calculation = xlManual in the
form activate event, the option buttons work fine. However, in order
for the rest of the form to perform properly it was necessary to force
a Calculate at certain points in my code. I put
Application.Calculation = xlAutomatic in the form's deactivate event.
If anyone has any explanations for what is happening here, I would
love to hear them.

Regards,

-Will