OptionButton: Boolean vs Long, and why -4146?

[Rufus Clupea]

Hi Folks,

I've been doing some reading up on the Multiple UserForm Controls with One Event Handler concept, and a few questions have arisen...

Some (most?) of the articles I've read talk about using Long variables vs. Boolean to hold the value of OptionButtons & CheckBoxes. The Long value of a selected OptionButton/CheckBox = 1 (makes sense) but they seem to like using -4146 for the un-selected value, mentioning 0 as an "also" possibility.

My questions are:

1. What would be the advantage (if any) in using Long vs. Boolean, and
2. Why -4146? (I'm thinking there's some interesting (hi)story behind this.)
3. Can I use the same Event Handler (EH) for several groups (Frames) of OptionButtons, or do I need to write different/separate EHs for each Frame of OptionButtons?

TIA

 

[Norie]

Are you sure you are looking at userform controls?

As far as I know on a userform option buttons have the value True or False.

As for using the same event handler for option buttons in frames, I suppose it depends on what the event handler does and how, if at all, the frame an option button is in has any bearing on matters.

 

[RoryA]

-4146 is the value of the constant xlOff which relates to Form controls on sheets, not the MSForms controls you use on a userform (or as ActiveX on a sheet).

 

[Rufus Clupea]

Are you sure you are looking at userform controls?

I thought I was (reading about) UserForm Controls; now I'm not so sure. I find the difference between Form Controls and Active-X Controls somewhat confusing. As I understand it now, the controls used on a Spreadsheet are Form Controls, while those used on a UserForm are Active-X(?) This seems (to me) counter-intuitive due to the naming.

Still, for future reference, it'd be nice to know the answers to my questions.

 

[Rufus Clupea]

-4146 is the value of the constant xlOff which relates to Form controls on sheets, not the MSForms controls you use on a userform (or as ActiveX on a sheet).

OK, any idea how/why they came up with that value, and/or why it's preferred by many authors?

I also notice (upon further reading—I'm trying to multitask here... ) that CheckBoxes seem to have a third state (mixed), which I was not aware of. Is the same true for OptionButtons?

 

[RoryA]

I don't know why they used that value.

If you're using Form controls, IMO you should use xlOn and xlOff as they are more meaningful. Some people prefer to use the numeric values, but there's no accounting for taste.

If you're using the MSForms controls (i.e. on a userform, or as ActiveX on a worksheet), then use True/False. If the TripleState property is set to True, then they can also be Null. The Form checkboxes can be set to Mixed, which is the same thing, but the Optionbuttons can't (unlike the Activex ones).

 

[Rufus Clupea]

If you're using the MSForms controls (i.e. on a userform, or as ActiveX on a worksheet), then use True/False. If the TripleState property is set to True, then they can also be Null. The Form checkboxes can be set to Mixed, which is the same thing, but the Optionbuttons can't (unlike the Activex ones).

I'm sorry—still confused. I have a test version set up—a UserForm with a Frame containing 10 OptionButtons. If I set the Triplestate property on one of the OptionButtons (or on a CheckBox—I just added one) to True, and leave the Value blank, they look very similar to a third-state CheckBox; they are selected, but the dot/checkmark are grayed-out. Doesn't this suggest a third state for both/either?

Both return "Null" in the immediate window.

 

[RoryA]

The Form checkboxes can be set to Mixed, which is the same thing, but the Optionbuttons can't (unlike the Activex ones).

Emphasis added.
If they're on a userform, they are MSForms controls, not Form controls.