Results 1 to 8 of 8

Thread: Check boxes

  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: Check boxes

    I am designing a form using VBA 6.3. I have everything running great except the check box state. The form has a number of text boxes & check boxes and 2 command buttons. One CMD button adds the data to a excel worksheet and clears all fields. The other CMD button closes the form.

    Once the fields are filled, the user submits the info and all fields should reset. All check boxes are ticked once the user hits the add CMD button. How do I clear the checkboxes??

    The code i am using to clear is -
    Me.hcc.Value = ""
    Me.pcc.Value = ""

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Checkbox values are true or false (or NULL if excel allows tristate, but I don't think it does). True and False are enumerated constants in VBA, equating to -1 and 0 respectively.

    What does all that mean? It means if you want to "clear" a checkbox, set its value to False. VBA will recognise False as a name representing the value 0. Similarly, to set them to "ticked" then assign the value True, which represents the value -1. You can also test the value of your checkboxes e.g.:
    Code:
    If Me.MyCheckbox.Value = True Then....
    which can be shorted to:
    Code:
    If Me.MyCheckbox Then....
    since Value is the default property of a checkbox and the conditional statement is met if the result is true. As such, asking "is the Value of the Checkbox equal to True?" is the same as simply "What is the value of the Checkbox?".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Yep, ActiveX checkboxes can have 3 values (True, False and Null).

    To allow a user to select all three values, the checkbox's TripleState property needs to be set to True. Even with the TripleState property set to False, the Null value can be assigned programmatically (as you will have seen when you tried Me.hcc.Value = "").

    If you want to clear all the checkboxes then you need to loop through the userform's controls, check if each control is a checkbox and then set it's value to False.

    Here are 2 examples, one using TypeOf and one using VBA.Typename()
    Code:
    Private Sub CommandButton1_Click()
    
        Dim ctrl As MSForms.Control
        
        For Each ctrl In Controls
        
            'is the control a checkbox?
            If TypeOf ctrl Is MSForms.CheckBox Then
                If Not TypeOf ctrl Is MSForms.OptionButton Then
                    If Not TypeOf ctrl Is MSForms.ToggleButton Then
                        'if it is a checkbox then untick it
                        ctrl.Value = False
                    End If
                End If
            End If
        Next ctrl
    
    End Sub
    Code:
    Private Sub CommandButton1_Click()
    
        Const CBX As String = "CheckBox"
        Dim ctrl As MSForms.Control
        
        For Each ctrl In Controls
            'is the control a checkbox then untick it
            If VBA.TypeName(ctrl) = CBX Then ctrl.Value = False
        Next ctrl
    
    End Sub

  4. #4
    Join Date
    Nov 2009
    Posts
    8

    check boxes still filled

    thanks for the info. I tried both the TypeOf and VBA.Typename methods but i am still getting ticked check boxes. The user selection is cleared but then all check boxes fill (as per the attached image).
    Attached Thumbnails Attached Thumbnails checkBox drama.png  

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Did you use the exact code I posted, or an adaptation?

  6. #6
    Join Date
    Nov 2009
    Posts
    8
    i used the exact code but just changed the name of the button.

  7. #7
    Join Date
    Nov 2009
    Posts
    8
    it's all fine now. i was making a mountain out of a molehill. All i needed to do was change the value of
    Me.hcc.Value = ""
    to
    Me.hcc.Value = False

    It works fine now. Thanks for all of your help.

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Glad you got it sorted, Cunnie. Good luck with the rest of your project.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •