Tristate test not working

[Sinbad]

Hi all, have a checkbox and need to test for its 3 states. true, false and null.

true and false are working fine, null just wil not work. any suggestions.

simple userform with a single checkbox and the following code attached to it for testing.

Private Sub CheckBox1_Click()
    If IsNull(CheckBox1.Value) Then
        CheckBox1.Caption = "Value is Null"
    ElseIf CheckBox1.Value = False Then
        CheckBox1.Caption = "Value is False"
    ElseIf CheckBox1.Value = True Then
        CheckBox1.Caption = "Value is True"
    End If

End Sub

 

[shg]

From XL97: How to Verify the Value of a CheckBox on a UserForm:

When you set the value of a CheckBox control to Null, the Click event for the check box is not executed. If you want to check for a Null value when you click a CheckBox control, use the Change event.

 

[Sinbad]

Looking into it...

no way of making my code work above ?

 

[Sinbad]

ok, that way works. but means i have to have 2 subs. one for the click event and one for the change event. Any way to combine both into one ?

 

[shg]

Why do you need the click event? If you click it, it changes.

 

[Sinbad]

I have three options i want to take care of with one button.

as follows..

the check box is labeled "Courier or local"
If true, hardware has to be sent away by courier.
if false, hardware is delivered localy
if null, user still needs to be determined if hardware stays local or has to be sent away. (see i can´t use the empty state as this means send it locally... the third greyed state is just perfect)

So the third state is used to prompt the user to take a decision. It does not have to be today, tomorrow etc.. just a visulal reminder that this specific item has no shipping attached to it yet.

So I want to use the three states to enter text into a cell, if null --> change its background and put the text ie "undetermined" into it. if it is true the cell will contain "courier" and transparent and if false the cell will have "Local shipment only" and yellow as background

if i use my code, i don´t seem to be able to get to the third state.-- NULL with a combination of yours and mine, i can get all three states but means i have 2 subs ??
the click event and one for the change event

Or how do i combine both ? that is the question...

 

[shg]

You've lost me.

Change the word Click to Change and the rest of your code works fine.

I'd use three option buttons instead of a checkbox.

 

[Sinbad]

Brilliant.. working. Thank you.

I´d like to use three buttons as well, but don´t have the space on the form to do that. Maybe we´ll re-design it someday, but for now I got what I needed. code as follows for those that want to do the same..

Private Sub CheckBox1_Change()
    If CheckBox1.Value = True Then
        CheckBox1.Caption = "Value is True"
        Label1.Caption = CheckBox1.Value
    ElseIf CheckBox1.Value = False Then
        CheckBox1.Caption = "Value is False"
        Label1.Caption = CheckBox1.Value
    Else 
        CheckBox1.Caption = "Value is Null"
    End If
End Sub

 

[shg]

Good job.