Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code for checkbox function (Excel 2000)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code for checkbox function (Excel 2000)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code for checkbox function (Excel 2000)

    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).ControlForm at
    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.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for checkbox function (Excel 2000)

    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.
    <pre>Sub CheckBoxClick()
    Dim oCell As Range
    Dim r As Long
    With ActiveSheet.Shapes(Application.Caller).ControlForm at
    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</pre>



    Thanks again.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code for checkbox function (Excel 2000)

    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?

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for checkbox function (Excel 2000)

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

Posting Permissions

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