Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting column
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/...bols.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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have B column turn red on conditional formatting of E column: | Excel Worksheet Functions | |||
Please help with Column to row formatting | Excel Worksheet Functions | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
formatting a column | Excel Worksheet Functions | |||
column to column conditional formatting won't work, need formula | Excel Worksheet Functions |