Formatting column

3 views
Skip to first unread message

Alice

unread,
Sep 18, 2003, 4:56:51 AM9/18/03
to
Hi all,

(1) Is it possible to format/make a column/cell that only
display a tick. When I click on it, a tick appear, when I
click again, the tick disappear.

(2) Can I make a excel sheet that allow me to key in
scores of students' tests (test 1 to no end) and as I
enter a new score, there will be a total sum displayed.

Thanks.

David McRitchie

unread,
Sep 18, 2003, 7:08:54 AM9/18/03
to
Hi Alice,
For the checkmark, format the column as "Wingdings 2", you
will either use a "P" or a zero length string if a formula,
or "P" or an empty cell if using VBA code.
http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm#ticks

To make the cell change when you click on it from displaying
a checkmark or looks empty you need an Normally one would
check the column to see if the macro should be making changes
and would mean changing the macro if you move columns around.
Since there is a different font, the test will be made on the font
rather than which columns are involved..

To install these Event Macros: Right-click on sheet tab, View Code. It
will only apply to the one sheet.

Since the font is different the macro will check the font rather
than what column was clicked giving you a bit more flexibility.

You could use a selection event but I think it is too much interfered
with if you workbook starts saving itself or CPU count is up, but
you can experiment by changing the first subroutine to selection.
Try out the following to see which you prefer double-click or
right-click and then remove the other one.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If LCase(Target.Font.Name) <> "wingdings 2" Then Exit Sub
If Len(Target.Value) > 1 Then Exit Sub
If Trim(Target.Value) = "" Then
Target.Value = "P"
Else
Target.Value = ""
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If LCase(Target.Font.Name) <> "wingdings 2" Then Exit Sub
If Target.Count > 1 Then Exit Sub 'number of cells selected
If Len(Target.Value) > 1 Then Exit Sub
If Trim(Target.Value) = "" Then
Target.Value = "P"
Else
Target.Value = ""
End If
Cancel = True 'normal RClick menus for cell are suppressed / this sheet
End Sub

-- second question---
=COUNTIF(D2:H2,"P") to count the number of checkmarks in a range
=SUM(D2:H2) SUM of numeric items in range
you can use both on the same range since the are working with different types
of data.

Some or all of this material will be available in
http://www.mvps.org/dmcritchie/excel/event.htm#ticks

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Alice" <tay_...@hotmail.com> wrote in message news:03e201c37dc2$cd593990$a001...@phx.gbl...

Reply all
Reply to author
Forward
0 new messages