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.

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


=IF(A20,$H$2,"")

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


Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=486297

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.

jwomack wrote:

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.

--

Dave Peterson

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=486297

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

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


Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=486297

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

On Fri, 18 Nov 2005 08:55:10 -0800, "jwomack"
wrote:

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.

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.

"Gord Dibben" wrote:

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

On Fri, 18 Nov 2005 08:55:10 -0800, "jwomack"
wrote:

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.

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

On Fri, 18 Nov 2005 12:52:05 -0800, "jwomack"
wrote:

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.

"Gord Dibben" wrote:

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

On Fri, 18 Nov 2005 08:55:10 -0800, "jwomack"
wrote:

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.

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

On Fri, 18 Nov 2005 15:58:42 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Fri, 18 Nov 2005 12:52:05 -0800, "jwomack"
wrote:

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.

"Gord Dibben" wrote:

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

On Fri, 18 Nov 2005 08:55:10 -0800, "jwomack"
wrote:

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.