Option buttons not responding

[Clive]

I've created a userform to be used as part of a survey. There will be
five questions each with five responses, represented by an option
button. I've grouped the option buttons via a group name and each
button is linked to a different worksheet cell.

By default, all linked cells are set to FALSE when the userform is
initialised or shown.

The problem is this: after the first button is selected, if another
button is selected it must be clicked twice to trult take effect. The
first click causes the first button to clear, the second click causes
the new button to show the black dot.

I had expected that selecting a second button would cause the first to
clear and the second to show the black dot all in one go.

To test, I created an identical userform in an empty workbook and it
behaved exactly as expected. I then exported the userform and imported
into the workbook where I truly wanted to use the userform and ...
back to the old behaviour.

Does this mean it's something to do with the workbook?

Any thoughs or suggestions on how I can stop this behaviour (which is
just about making the userform unusable) would be appreciated.

Thanks,

Clive

 

[GS]

After serious thinking Clive wrote :

I've created a userform to be used as part of a survey. There will be
five questions each with five responses, represented by an option
button. I've grouped the option buttons via a group name and each
button is linked to a different worksheet cell.

By default, all linked cells are set to FALSE when the userform is
initialised or shown.

The problem is this: after the first button is selected, if another
button is selected it must be clicked twice to trult take effect. The
first click causes the first button to clear, the second click causes
the new button to show the black dot.

I had expected that selecting a second button would cause the first to
clear and the second to show the black dot all in one go.

To test, I created an identical userform in an empty workbook and it
behaved exactly as expected. I then exported the userform and imported
into the workbook where I truly wanted to use the userform and ...
back to the old behaviour.

Does this mean it's something to do with the workbook?

Any thoughs or suggestions on how I can stop this behaviour (which is
just about making the userform unusable) would be appreciated.

Thanks,

Clive

It's possible that the workbook has become corrupted. Try exporting all
its components to a new workbook and see if the problem ceases.

 

[Jim Rech]

I don't suppose there is any Worksheet_Change or Workbook_SheetChange event
handler running?

Jim

 

[ajlowndes]

Just a guess...
I had some weird Optionbutton behaviour when i set "ShowModal" to
False for that Userform (which, as I understand it, allows the user to
edit content in the workbook while the userform is open.
Unfortunately it also made a lot of my OptionButtons not work - i.e.
the one selected would remain with the value "false" when I clicked my
OK button. I've since set ShowModal back to "True")
I haven't investigated fully why ShowModal does this weird behaviour,
but it's worth looking at from your end I guess

 

[Clive]

Thanks for these suggestions.

I did have a worksheet_change event code for a worksheet unconnected
with the control sources for the option buttons.

ShowModal is TRUE

I've tried to rebuild the workbook from scratch: first a "Data" sheet
for the control sources connected with the option buttons on the
UserForm1. Works as expected.

Added simple data to second sheet, named "Staff": column B header is
"Name", column C header is "Staff Number". 7 sample staff names and
numbers included. Userform works as expected.

When I collect the staff names and numbers from a central source, some
of the numbers are actually labels so column D header is "Numerical
Staff Number" and I have a formula to check if the column C entry is a
value, and convert text entries to values.

The cell formula for row 2 is:
=IF(ISBLANK(B2),"",IF((CELL("type",C2)="v"),C2,VALUE(C2)))

The first ISBLANK is present becaused I hope to make this as dynamic
as possible due to many staff changes :-(

As soon as one instance of this formula is included in the "Numerical
Staff Number" column the userform misbehaves.

I've changed the formula to:
=IF(ISBLANK(B2),"",IF(ISNUMBER(C2),C2,VALUE(C2)))

This formula does not cause the userform to misbehave.

Does this point to somehting about the CELL() function?

Any other thoughts?

All contributions appreciated, thanks,

Clive

 

[Clive]

Thanks for these suggestions.

I did have a worksheet_change event code for a worksheet unconnected
with the control sources for the option buttons.

ShowModal is TRUE

I've tried to rebuild the workbook from scratch: first a "Data" sheet
for the control sources connected with the option buttons on the
UserForm1. Works as expected.

Added simple data to second sheet, named "Staff": column B header is
"Name", column C header is "Staff Number". 7 sample staff names and
numbers included. Userform works as expected.

When I collect the staff names and numbers from a central source, some
of the numbers are actually labels so column D header is "Numerical
Staff Number" and I have a formula to check if the column C entry is a
value, and convert text entries to values.

The cell formula for row 2 is:

=IF(ISBLANK(B2),"",IF((CELL("type",C2)="v"),C2,VALUE(C2)))

The first ISBLANK is present because I hope to make this as dynamic as
possible due to many staff changes :-(

As soon as one instance of this formula is included in the "Numerical
Staff Number" column the userform misbehaves.

I've changed the formula to:

=IF(ISBLANK(B2),"",IF(ISNUMBER(C2),C2,VALUE(C2)))

This formula does not cause the userform to misbehave.

Keeping the new formula I moved to the next column, a sorted version
of the numerical staff number list - this will be used to provide
validated data to a cell from which staff will select their staff
number prior to completing the survey.

I create the sorted staff number list using the formula: =SMALL($D$2:$D
$87,ROW()-1)

The userform behaves.

If I change the formula to: =SMALL(OFFSET($D$2,0,0,$I$1,1),ROW()-1)

where $I$1 holds the number of non-zero entries in column B, the staff
name column, then the userform misbehaves once more.

So the behaviour of the userform appears, to my simple mind, to be
dependent on cell formulae in worksheets.

Can anyone shed light on this behaviour?

I can submit a simplified version of the workbook if anyone is
interested.

All contributions appreciated, thanks,

Clive

 

[GS]

ajlowndes explained on 11/4/2010 :

Just a guess...
I had some weird Optionbutton behaviour when i set "ShowModal" to
False for that Userform (which, as I understand it, allows the user to
edit content in the workbook while the userform is open.
Unfortunately it also made a lot of my OptionButtons not work - i.e.
the one selected would remain with the value "false" when I clicked my
OK button. I've since set ShowModal back to "True")
I haven't investigated fully why ShowModal does this weird behaviour,
but it's worth looking at from your end I guess

Just for the record...
VBA boolean value TRUE = -1
VBA boolean value FALSE = 0

Userform Show method constant vbModal = 1
Userform Show method constant vbModeless = 0

VBA boolean value TRUE = '-1'. This is not the value of the VBA
constant vbModal, which is '1', that is expected when specifying the
Modal arg.

So using...
Userform1.Show TRUE '-1
is not correct syntax, and will probably cause unexpected behavior.

Using...
Userform1.Show vbModal '1
is the correct syntax to use, but since MSO VBA userforms are modal by
default this arg never needs to be specified.

Boolean value FALSE = '0', which is the same as the constant
vbModeless. Either can be used when specifying the modal arg when
modeless is desired.

HTH