Excel checkbox

Question

Hello

I am trying to create a form on Excel using checkboxes. 

I need each each box to either be blank, have a green tick or have a red cross. 

Is there a way to do this using check boxes?

thanks

Answer 1

I am not able to insert a cross or change the tick color. However, I have been successful in changing the color of the checkbox background with the following event code that runs when the value of the checkbox is changed. Note that the Checkbox TripleState property must be set to True.

The color hex code can be obtained by setting the background color in the Properties and then copy the hex code from the properties dialog and paste into you VBA code. The actual hex code might shorten when pasted into the VBA code but that is OK because it is only removing superfluous characters.

Note that the events are Change events; NOT Click events.

Private Sub CheckBox1_Change()

    Select Case CheckBox1.Value

        Case True

            CheckBox1.BackColor = &HFF00&

            Application.ScreenUpdating = True

        Case False

            CheckBox1.BackColor = &HFF&

             Application.ScreenUpdating = True

        Case Else

            If IsNull(CheckBox1.Value) Then

                CheckBox1.BackColor = &HFFFFFF

                Application.ScreenUpdating = True

            End If

    End Select

End Sub

Private Sub Checkbox2_Change()

    Select Case CheckBox2.Value

        Case True

            CheckBox2.BackColor = &HFF00&

            Application.ScreenUpdating = True

        Case False

            CheckBox2.BackColor = &HFF&

             Application.ScreenUpdating = True

        Case Else

            If IsNull(CheckBox2.Value) Then

                CheckBox2.BackColor = &HFFFFFF

                Application.ScreenUpdating = True

            End If

    End Select

End Sub

Private Sub Checkbox3_Change()

    Select Case CheckBox3.Value

        Case True

            CheckBox3.BackColor = &HFF00&

            Application.ScreenUpdating = True

        Case False

            CheckBox3.BackColor = &HFF&

             Application.ScreenUpdating = True

        Case Else

            If IsNull(CheckBox3.Value) Then

                CheckBox3.BackColor = &HFFFFFF

                Application.ScreenUpdating = True

            End If

    End Select

End Sub