PDA

View Full Version : Solved: Changing Option Button Value



Belch
09-12-2006, 06:25 AM
Hi all,

I have a userform with three option buttons (call them optA, optB and optC) in the same group (i.e. only one of them can be selected).
I also have some checkboxes that all relate to one of the option buttons (say optC). I have written VBA so that if a user clicks any of the checkboxes, optC becomes selected.

There are a couple of problems though.
Firstly, when a checkbox is clicked and optC becomes selected, it does not unselect the other option buttons. So if optA was selected, then a checkbox was clicked, optA will stay selected but now optC is selected as well.
Secondly, when optC becomes selected, it appears as a grey dot in the actual control rather than a normally selected black dot. I'm guessing this means that when an option button is selected via VBA it is not truly selected?

Below is the VBA to select optC:

Private Sub chk1_Click()
If chk1.Value = -1 Then
optC.Value = -1
End If
End Sub


I wouldn't have thought I need to set optA and optB.Value to 0 (unselected) as they are option buttons in the same group, but even if I do this it still doesn't register optC as selected.

Any advice appreciated, thanks.

mvidas
09-12-2006, 10:46 AM
Hi Neil,
Strangely (well, not completely), -1 isn't what you want to use for the option value: optC.Value = True '-1Should take care of it.
Matt

Bob Phillips
09-12-2006, 11:06 AM
OK Matt, explain why -1 doesn't work then. By my understaind the numeric equivalent of TRue in VBA is -1, and if you type

?TRUE=-1

in the immediate window, it returns True.

mvidas
09-12-2006, 11:37 AM
The funny thing is, when I read the email I guess I missed who wrote your comment, I thought it was Neil, and I thought about calling you into this thread (based on the weekend thread about TRUE in excel) :)

The value of TRUE in vba is -1
?clng(true)
-1
But the optionbutton value's property can have only three choices: true, false, null. Null is what gives you the greyed value (you can get the same by changing the .value to "on", "yes", "hi bob how are you", etc). Changing the value to -1 produces the null value, since -1 isn't the exact same as true. If you set optC.value=cbool(-1) You'll get the action desired, because it is converting -1 to the boolean value of true.

mvidas
09-12-2006, 11:44 AM
Example attached

MWE
09-12-2006, 03:34 PM
The funny thing is, when I read the email I guess I missed who wrote your comment, I thought it was Neil, and I thought about calling you into this thread (based on the weekend thread about TRUE in excel) :)

The value of TRUE in vba is -1
But the optionbutton value's property can have only three choices: true, false, null. Null is what gives you the greyed value (you can get the same by changing the .value to "on", "yes", "hi bob how are you", etc). Changing the value to -1 produces the null value, since -1 isn't the exact same as true. If you set optC.value=cbool(-1) You'll get the action desired, because it is converting -1 to the boolean value of true.this is a rather interesting thread. I had always assumed that the numerical equivalent of True was 1 and the numerical equivalent of False was 0. I have used that assumption in dozens of applications where booleans and numerics were handled somewhat casually and it always worked.

Belch
09-13-2006, 12:17 AM
Thanks for that, it seems to work fine now.

I took an extract from the Excel VBA help file, under the Value property:



CheckBox
An integer value indicating whether the item is selected:
Null Indicates the item is in a null state, neither selected nor cleared (mk:@MSITStore:C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1033\fm20.chm::/html/IDH_f3defClear.htm).
–1 True. Indicates the item is selected.
0 False. Indicates the item is cleared.

OptionButton
Same as CheckBox.


I guess it does show True for selected, but it also shows -1 which is obviously wrong. Nice to know MS know their own programs :)

Thanks for the help folks.

Bob Phillips
09-13-2006, 03:58 AM
this is a rather interesting thread. I had always assumed that the numerical equivalent of True was 1 and the numerical equivalent of False was 0. I have used that assumption in dozens of applications where booleans and numerics were handled somewhat casually and it always worked.

The numerical equivalent of TRUE in computer languages has always (AFAIK) been -1 (all bits set), and 0 for FALSE (all bits unset).

In Excel for some odd reason, any non-zero value resolves to TRUE (try putting a value in a cell and ?CBool(Activecell.Value) in the immediate window), and 0 resolves to False, but conversely TRUE resolves to 1 (aka the SUMPRODUCT evolution).

mvidas
09-13-2006, 05:25 AM
Bob, that's TRUE!

Neil, if you like that, take a look at the TripleState property in vba

TripleState Property
Determines whether a user can specify, from the user interface, the Null (javascript:hhobj_5.Click()) state for a CheckBox or ToggleButton.
Syntax
object.TripleState [= Boolean]
...
Although the TripleState property exists on the OptionButton, the property is disabled.
...
Regardless of the property setting, the Null value can always be assigned programmatically to a CheckBox or ToggleButton, causing that control to appear shaded.
Sub HelpPopup (sFile, sID)Call InitConstants() On Error Resume Nextr=oua.HelpPopup(sFile,sID)If Err0 Then Msgbox L_SECURITY_MSG,48,L_TITLEEnd Sub

Belch
09-15-2006, 05:13 AM
I guess things aren't always black & white/true & false/on & off/one & zero/etc in computing after all.