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.