Gaffa
Here's one way:
Assuming cells E2:E12 are the "tick"-cells and E13
the summation cell.
1. Select E2:E12 and format with font "Wingdings 2"
2. Right click the sheet tab and choose "View codes"
3. Copy and paste the below code to the rightmost window
Private Sub Worksheet_Change(ByVal Target As Range)
'Leo Heuser, 29 June 2004
Dim Cell As Range
Dim TickRange As Range
Set TickRange = Range("E2:E12")
On Error GoTo Finito
If Union(Target, TickRange).Address = _
Union(TickRange, TickRange).Address Then
Application.EnableEvents = False
For Each Cell In Target.Cells
If Cell.Value = "Y" Or Cell.Value = "y" Then
Cell.Value = Chr(80) '"P"
Else
Cell.ClearContents
End If
Next Cell
End If
Finito:
Application.EnableEvents = True
On Error GoTo 0
End Sub
The code will be activated every time the contents of
a cell is changed. It then checks to see, if the cell is
in the "tick"-range, checks if a "y" or "Y" is entered,
and if it is, enters a "P" instead. "P" display as a
tick, when the "Wingdings 2" font is used. If anything
else but "y"s are entered, the cell is blanked.
4. In E13 enter this formula to count the number
of ticks:
=COUNTIF(E2:E12,"P")
Actually the formula also counts lower case "p"s,
but who cares in this situation
--
Best Regards
Leo Heuser
Followup to newsgroup only please.
Gaffa said:
I want to be able to type "y" or "Y" in a cell,but instead oe the y or Y
appearing a 'tick' will appear instead. Then, at the bottom of the colum the
'ticks' will be totalled. I will then carry this total over to another
sheet.
