Checkbox help

lorib01

New Member
Joined
Nov 13, 2006
Messages
37
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.
 
Thanks, that code works great! I really appreciate it.

I added another macro that puts the date that the box was checked into the form (this solves the issue of having to change the form to get the color to change). The only problem is that I have to add it to each check box. I'm sure their's a way to make it run on the entire sheet. Can you please take a look at it and help.

Thanks


Code:
Sub Process_CheckBox()

    Dim cBox As CheckBox
    Dim LRow As Integer
    Dim LRange As String

    LName = Application.Caller
    Set cBox = ActiveSheet.CheckBoxes(LName)
    
    'Find row that checkbox resides in
    LRow = cBox.TopLeftCell.Row
    LRange = "N" & CStr(LRow + 1)
    
    'Change date in column N, if checkbox is checked
    If cBox.Value > 0 Then
        ActiveSheet.Range(LRange).Value = Date
        
    'Clear date in column N, if checkbox is unchecked
    Else
        ActiveSheet.Range(LRange).Value = Null
    End If
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
This code will run both processes when run from a macro button:
Code:
Sub RunBothProcess()
Dim LRow As Integer
Dim LRange As String
Dim sp As Shape
    For Each sp In ActiveSheet.Shapes
        'Shades All Checked "Forms" Checkboxes
        If sp.Type = msoFormControl Then
            With sp.OLEFormat.Object
            If sp.FormControlType = xlCheckBox Then
            .ShapeRange.Fill.Visible = IIf(Sgn(.Value) > 0, -1, 0)
            .ShapeRange.Fill.ForeColor.SchemeColor = 22 '15
            LRow = sp.TopLeftCell.Row
            LRange = "N" & CStr(LRow + 1)
                If sp.OLEFormat.Object.Value > 0 Then
                    ActiveSheet.Range(LRange).Value = Date
                Else
                    'Clear date in column N, if checkbox is unchecked
                    ActiveSheet.Range(LRange).Value = Null
                End If
            End If
            End With
        End If
    Next sp
End Sub
This assumes all your checkboxes are from the Forms Toolbar.
 
Upvote 0

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top