PDA

View Full Version : Solved: Cells to Checkbox's?



LarryLaser
10-08-2006, 09:19 PM
Is it possible to make all cells in a single spreadsheet as check box's?

Details;
WorkBook designed as a Grade Book with the first Sheet as an Attendance Sheet, each cell represents a single day with "check" for present and "NO check" for absence. In other words, Boolean (true or false).

If so, can a function be applied to each cell, so when you are in an active cell you type "Y" for present (check mark) or "N" for absent (no check mark) and return/enter is automatic (moves to next row/cell below).

At the end of a period (9 weeks) use the data from each sheet (Including attendance) to create a Chart / Report for each student (parent eval).

Jacob Hilderbrand
10-08-2006, 09:59 PM
Try this. Put the code in the sheet code module for the sheet(s) you want it to run on.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C3:L29")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False

If UCase(Target.Text) = "Y" Then
Target.Value = "?"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If

Application.EnableEvents = True

End Sub

LarryLaser
10-08-2006, 10:40 PM
Very Nice Master
You must have a very high count of MidiClorians. :giggle

Jacob Hilderbrand
10-09-2006, 12:24 AM
Glad to help :beerchug:

P.S. I really hate that they defined how the Force was obtained...

Len Piwowar
10-09-2006, 04:29 AM
I thought the above code was very nice, I thought I'd put my two cents in for those of us who like to click! Double click turns on check mark and if check mark exists one click turns it off!


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("C3:L29")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Target.Value = "?"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("C3:L29")) Is Nothing Then
Exit Sub
End If

If Target.Value = "?" Then
Target.ClearContents
End If
End Sub