Checkbox help

[lorib01]

Hello,

I've been trying to make a spreadsheet with 55 checkboxes. I need to be able to see quickly which boxes are checked so I wanted to add a macro that changes the color of the field when the box is checked and return it to white when unchecked. (i.e. C2 changes to grey when checkbox in C2 is checked). This seems like a simple script but I seem to be unable to write it. Can anyone help me with this?

Thanks in advance.

 

[Datsmart]

If your checkboxes are from the Control ToolBar, this code will shade the Upper Left cell where the checkbox resides.

Sub ShadeCheckBoxCell()
Dim sh As Shape
Application.ScreenUpdating = False
    For Each sh In ActiveSheet.Shapes
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then
                If sh.OLEFormat.Object.Object = True Then
                sh.OLEFormat.Object.TopLeftCell.Interior.ColorIndex = 15
                Else
                sh.OLEFormat.Object.TopLeftCell.Interior.ColorIndex = xlNone
                End If
            End If
        End If
    Next sh
End Sub

 

[lorib01]

not working

Thanks but I don't seem to be able to get your script to run. Nothing happens when I click the check box except the box becomes checked or unchecked. I did write the following script this weekend but I think it would be much easier to use a variable rather than recreating it for each individual check box.

Private Sub CheckBox1_Click()

If Sheet1.CheckBox1.Value = True Then
Sheet1.CheckBox1.BackColor = &HE0E0E0
Else
Sheet1.CheckBox1.BackColor = &HFFFFFF
End If

End Sub

 

[Datsmart]

Using your style of code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Shape
Application.ScreenUpdating = False
    For Each sh In ActiveSheet.Shapes
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then
                If sh.OLEFormat.Object.Object = True Then
                sh.OLEFormat.Object.Object.BackColor = &HE0E0E0
                Else
                sh.OLEFormat.Object.Object.BackColor = &HFFFFFF
                End If
            End If
        End If
    Next sh
End Sub

This code will not run when a checkbox is changed, only when the worksheet is changed. The only Event code for a checkbox that I am aware of is the click Event that requires each checkbox to have their own code.
Add this code to the worksheet module you want the code to run in
Right click the Sheet Tab, choose View Code, paste code in the right side panel.
Place cursor in any empty cell and hit the Delete key, that will trigger a Change Event and the code will run.

 

[lorib01]

I'm sorry, I must be doing something wrong, this code doesn't seem to do anything either. I'm sure it's written well because the debugger doens't catch anything, so it must be me.

I right clicked on the sheet tab, selected "veiw code" replaced the exsisting code with the code you sent, clicked in an empty cell, hit delete but when I put a check in the box nothing changes. I tried saving the sheet after selecting a checkbox but it didn't help, I tried making other changes to the sheet but still nothing.

I'm not sure what I'm doing wrong.

Thanks

 

[daniels012]

This worked Fantastically for me!

I get no credit though! Erik created it as I have left his name in the code:

Sub AddColorToCheckbox()

'Erik Van Geit
'061024
'assign this code to any FORMS-checkbox
'fillcolor will be visible if checked
'assuming you assigned a color
'else enable the line ForeColor

Dim ThisShape As Shape
Set ThisShape = ActiveSheet.Shapes(Application.Caller)

    With ThisShape.OLEFormat.Object
    .ShapeRange.Fill.Visible = IIf(Sgn(.Value) > 0, -1, 0)
    '.ShapeRange.Fill.ForeColor.SchemeColor = 43
    End With

End Sub

Michael

 

[lorib01]

Hello, Did I mention I'm a complete newbie to this. I am (sorry) and I really appreciate the help but I can't get these codes to work. I'm certain I'm doing something wrong in trying to initialise the code or something.

This code gives me Error 13 code mismatch errors

I ran the debugger and it highlighted the line

Set ThisShape = ActiveSheet.Shapes(Application.Caller)

now when I click on a box it returns the error "Cannot exicute in break mode."

I'm completely lost.

Thanks

 

[Tom Urtis]

Do this please:

Press Alt+F11

Press Alt + Q

Click OK to the prompt "This command will stop the debugger"



Then answer 2 questions:

(1)
How were the checkboxes created...from the control toolbox toolbar, or from the Forms toolbar? If you are not sure, then right-click on one and see if "Properties" is an item in that pop-up menu.

(2)
What is the name of the worksheet where these checkboxes are located.

 

[Datsmart]

This code will Shade ALL checked "CheckBoxes" for both Forms and Control Toolbar check boxes.

Private Sub Worksheet_Change(ByVal Target As Range)
'Most code ideas from:
'Erik Van Geit
'061024
Dim sh As Shape
Dim rng As Range
    For Each sh In ActiveSheet.Shapes
        'Shades All Checked "Forms" Checkboxes
        If sh.Type = msoFormControl Then
            With sh.OLEFormat.Object
            If sh.FormControlType = xlCheckBox Then
            .ShapeRange.Fill.Visible = IIf(Sgn(.Value) > 0, -1, 0)
            .ShapeRange.Fill.ForeColor.SchemeColor = 22 '15
            End If
            End With
        End If
        'Shades All Checked "Control Toolbar" Checkboxes
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then
                If sh.OLEFormat.Object.Object = True Then
                sh.OLEFormat.Object.Object.BackColor = &HE0E0E0
                Else
                sh.OLEFormat.Object.Object.BackColor = &HFFFFFF
                End If
            End If
        End If
    Next sh
End Sub

Add this code to the worksheet module you want the code to run in.
Right click the Sheet Tab, choose View Code, paste code in the right side panel.

Note: This will not change when the check box is checked or un-checked. It will only alter the checkbox background color when the Worksheet itself is changed, such as hitting the Delete key in an empty cell.

 

[lorib01]

I stopped the debugger, thanks.

I actually created 2 spread sheets (book2 and book3, I'll rename the one that works) and I'm working on Sheet 1 in both of them. Book 2 I used the form tool bar then in book3 I used the control. Book 3 takes longer to load than book 2 which I find interesting.

Thank you