How do i have a check display with any data entry in a cell

J

jwomack

I am creating a simple spreadsheet for teachers and want to have only a check
mark displayed regardless of what they type into a cell.
 
S

SteveG

Insert the check mark symbol you want from the Insert-Symbol menus int
a reference cell for your formula. Then your formula in B2 would be.


=IF(A2>0,$H$2,"")

A2 is where the data is entered by the teachers, $H$2 is where yo
inserted the check mark symbol for your reference. You can then dra
this down for each row of data.


Cheers,

Stev
 
D

Dave Peterson

Select the range
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings.
 
S

SteveG

Sorry, that formula excludes inserting the check mark if the teacher
types in a 0. Use this if that is a concern.

=IF(ISBLANK(F31),"",$J$31)

Steve
 
S

SteveG

Sorry again! I copied that out of my spreadsheet and of course I wa
not using the same cell references.

=IF(ISBLANK(A2),"",$H$2)


Cheers,

Stev
 
G

Gord Dibben

You could use event code behind the worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Value = "a"
.Font.Name = "Marlett"
.FontStyle = "Regular"
.Size = 10
End With
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the event code into that module.

As written it operates on first 8 columns only.


Gord Dibben Excel MVP
 
J

jwomack

That is what I wanted but how do I get it to work on only a part of the
worksheet? For instance, if you are looking at a teacher's gradebook, it
would be columns D-I and rows 3-25.
 
G

Gord Dibben

Try this amended code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
Application.ExtendList = False 'to prevent Marlett font extending
'out of Target Range
If Not Application.Intersect(Range("D3:I25"), Target) Is Nothing Then
With Target
.Value = "a"
.Font.Name = "Marlett"
.FontStyle = "Regular"
.Size = 10
End With
Application.ExtendList = True
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord
 
G

Gord Dibben

I like this version a little better.

Other version had a bug that would not allow deletion of data from the range.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
Application.ExtendList = False
If Not Application.Intersect(Range("D3:I25"), Target) Is Nothing Then
If Target.Value <> "" Then 'added line
With Target
.Value = "a"
.Font.Name = "Marlett"
.FontStyle = "Regular"
.Size = 10
End With
Application.ExtendList = True
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord
 
Top