OptionButton: Boolean vs Long, and why -4146?

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
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... :confused:

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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
-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).
 
Last edited:

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
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. :confused:

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

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85

ADVERTISEMENT

-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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Feb 11, 2019
Messages
85
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).

:confused: 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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top