Hide marks from students in Excel 4

[jomtones3]

I'm keeping a tracking sheet of work my students have done in Excel, and want to be able to show them what they have handed in without showing them everybodys marks!

Currently I'm doing a find/replace to replace each mark with an 'x' before saving as a new file and then showing them the sheet, but this is rather laborious, is there any way I get get Excel to show a 'filtered' version with their marks (eg. d=distinction, p=pass) hidden by a mark of some sort?

Thanks!

 

[KenWright]

What's wrong with using Data / Filter / Autofilter on student name and then filtering on just them?

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jomtones3]

That's good but they won't get the impetus of seeing who's in the lead then! It's very handy for them to be able to see roughly how far the other students have got...

 

[KenWright]

So you have a list in some sort of order that denotes ranking, but you don't want them to see actual marks - is that correct.

If so then why not just hide the column with the marks before you show them the list?

What sort of format is your data? Can you give us a dummied down example?

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jomtones3]

Problem is the marks are all over the page - see here:

Thanks!

 

[sahmiele]

Click on your tab with the information in it. Then click the Visual Basic Editor, Double-click on Sheet1, and put this code into it. When you double-click a student and hit Enter, it will create a copy of your spreadsheet on sheet2, leave the names and ranking, but clear out all of the marks. This way you have your sheet that you can work on, and one to show the kids. Hope it helps!



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Name = "Sheet1" Then
If ActiveCell.Column = 1 Then

studentrow = ActiveCell.Row - 1
Sheets("Sheet2").Select
Selection.ClearContents
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Range("D9").Select
If studentrow = 3 Then
Range("B4:Z100").ClearContents
Else
Toprange = "B3:z" & studentrow
Bottomrange = "B" & studentrow + 1 & ":B100"
Range(Toprange, Bottomrange).ClearContents
End If
End If



End Sub

 

[firefytr]

sahmiele,

Don't forget to use native objects when using event codes. You can change out the activesheet for "Me" as you're in the worksheet module, and activecell for Target - they are VERY different. Also, you left out an End If line ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim TopRange As String, BottomRange As String
    If Me.Name = "Sheet1" Then
        If Target.Column = 1 Then
            Sheets("Sheet2").Cells.ClearContents
            Me.Cells.Copy Destination:=Sheets("Sheet2").Range("A1")
            Application.CutCopyMode = False
            If Target.Row - 1 = 3 Then
                Me.Range("B4:Z100").ClearContents
            Else
                TopRange = "B3:Z" & Target.Row - 1
                BottomRange = "B" & Target.Row & ":B100"
                Range(TopRange, BottomRange).ClearContents
            End If
        End If
    End If
End Sub

HTH

-----------
Regards,
Zack Barresse

 

[jomtones3]

Wow this looks pretty awesome! IS there any way I can get it to replace the other students marks with an 'x' instead of clearing them?

Thankyou!

 

[KenWright]

What I'm trying to understand is the layout of the data, but you have blitzed the names section so now I can't see whether one row of data is for one student, or whether each student has multiple rows of data.

If every row of data for a student has the students name against it, then I was thinking you could use a single cell on the sheet to type in the student name and via conditional formatting it will change the font on all other students marks to make them disappear, but the colouring may put paid to that.

That having been said you could still use the same approach but combine with Zacks code and have it turn the font colour the same as the background colour.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Hi,

1. Set up a list of students. I NAMED this RANGE StudentList

2. On your report, lets say in A1, do Data>Validation - List and enter StudentList.

3. now you can select any student from the list in A1.

4. select the area you want to format (the data/font for other students will be shaded WHITE to make it invisible)

5. With that area selected -- Format>Conditional Formatting -- Formula is:=$A$1<>$A3 and shade the font WHITE.

VOLA!

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[SkipVought]

oops

the
[tt]
=$A$1<>$A3
[tt]
$A3 value should reference the first row in the selected area. So if your selected area begins in B7, then the forumula should be
[tt]
=$A$1<>$A[red]7[/red]
[/tt]

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[KenWright]

Hi Skip - That's where I was heading in my note, but hence my caveat re colours. On the coloured sections of the sheet as per the example, even a white font will be visible.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Then maybe what you do is FIRST, shade the FONT and INTERIOR identically.

Then use Conditional Format to shade the FONT to display.

CAVEAT: Also need a CF to show all.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[Rofeu]

Hi,

can you not name your range that lists the marks (I used "NamedRange" as example) and run the following code:

Sub Marks_to_X()

    ThisWorkbook.Names("NamedRange").RefersToRange. _
        SpecialCells(xlCellTypeConstants).Value = "X"

End Sub

Cheers,

Roel

 

[xlhelp]

With all these people helping, I feel I am encroaching.

I teach and go through similar problems of showing students their marks.

If you like you can send me your file to xlwrdhlpATyahooDOTca.

Unfortunately I cannot look at it until Monday.

Stupidity has no handicap