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

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(A2>0,$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

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

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

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

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"
<jwomack@discussions.microsoft.com> 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"
> <jwomack@discussions.microsoft.com> 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"
<jwomack@discussions.microsoft.com> 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"
>> <jwomack@discussions.microsoft.com> 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"
><jwomack@discussions.microsoft.com> 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"
>>> <jwomack@discussions.microsoft.com> 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.
>>>
>>>