Code for checkbox function (Excel 2000)

Topic

New Reply

I have a training worksheet with 7 rows of data for each of the 12 months. See attachment
After the user double clicks in the red area to select a model in the top row, they have the option of checking a box to indicate that the student is trained.
What I’m trying to do next is:
1. After the model is selected, to populate the remaining red cells below in the same column with the model data selected in the top row.
2. If trained is checked off for any model, in any row, the remaining trained checkboxes below it will also be checked. (once you’re trained, you would never uncheck the box in the following months)
Thanks for looking,
Scott

Reply | Quote

Replies

For #1: change the On Click event procedure for the OK button to

Private Sub OKButton_Click()
Dim r As Long
For r = ActiveCell.Row To 91 Step 8
Cells(r, ActiveCell.Column) = ListBox1.Value
SetFont Cells(r, ActiveCell.Column)
Next r
Unload Me
End Sub

You must change SetFont accordingly:

Sub SetFont(oCell As Range)
With oCell.Font
.Name = “Arial”
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

Reply | Quote

For #2: Create the following macro in a standard module:

Sub CheckBoxClick()
Dim oCell As Range
Dim r As Long
With ActiveSheet.Shapes(Application.Caller).ControlFormat
If .Value = 1 Then
Set oCell = Range(.LinkedCell)
For r = oCell.Row + 8 To 91 Step 8
Cells(r, oCell.Column) = 1
Next r
End If
End With
End Sub

Assign this macro to all check boxes. The fastest way to do this:
Press Ctrl+G
Click Special…
Select Objects
Click OK.
You have now selected all check boxes.
Right-click the border of one of the selected check boxes.
Select Assign Macro from the popup menu.
Select CheckBoxClick from the list and click OK.

Reply | Quote

Hans,
Thanks for the help. I also modified the second macro so if the checkbox is unchecked(checked by mistake),
the cells below also uncheck.

Sub CheckBoxClick()
    Dim oCell As Range
    Dim r As Long
    With ActiveSheet.Shapes(Application.Caller).ControlFormat
        If .Value = 1 Then
            Set oCell = Range(.LinkedCell)
            For r = oCell.Row + 8 To 91 Step 8
                Cells(r, oCell.Column) = 1
            Next r
        ElseIf .Value < 1 Then
            Set oCell = Range(.LinkedCell)
            For r = oCell.Row + 8 To 91 Step 8
                Cells(r, oCell.Column) = 0
            Next r
        End If
    End With
End Sub

Thanks again.

Reply | Quote

Are you sure you want to uncheck all check boxes below the one that has been cleared? Shouldn’t that be the check boxes above it?

Reply | Quote

Hans,
It could be looked at either way. The way this application will be used, the boxes below should be cleared.
Always learning,
Scott

Reply | Quote