Help with school data sheet - debugging

[mj_bowen]

Hi,

I am trying to sort data in a school assessment table so that it can be sorted by column headers. When working properly, this sheet will have over 350 names on it and will be an invaluable resource for our schools teachers - at least thats the plan!

With lots of help from 'VoG' (a Mr. Excel guy - thank you very much again), the sheet now sorts the data but will not sort Year groups in the order 2, 1 , Fn. (rather Fn, 2, 1)

Also when ever I click in any other cell I get a run-time error message instead of being able to type directly into the cell...?
I would like it so that the only cells that do the sorting are the column headers - not any other data cells
I am really stuck, I have tried all I know but to no avail. (VoG - Mr Excel helped all he could)

Please find an attached link,

https://www.box.com/s/8161b2f9bd8213e86ba3

Thank you for your help,
Matt

 

[drsarao]

AFAIK, Fn will always reverse sort to top as per Excel sort algorithm.
Easiest way will be to use 0 (zero) instead of "Fn".
You can always display "Fn" instead of zero in the worksheet using custom format --> "[=0]F\n;0"

 

[mj_bowen]

AFAIK, Fn will always reverse sort to top as per Excel sort algorithm.
Easiest way will be to use 0 (zero) instead of "Fn".
You can always display "Fn" instead of zero in the worksheet using custom format --> "[=0]F\n;0"

Hi,
Thank you for your reply!

I tried your suggestion and it did work in the 'origin' cell - however the cell containing the [=0]F\n;0 code is part of the source for
a drop down menu and still returns a '0' (zero) in the destination cells?

Have I missed something?

regards,

Matt

 

[mj_bowen]

..sorry - I just formatted all of the cells in the column and they now return Fn - brilliant!

You have made my evening!

thanks again,

Matt

 

[mj_bowen]

Hi again,

I wondered if you could just look at the sheet again as I cant seem to figure out
why the rows sort when you click on a childs name.
I would like to be able to just click on the table headers to sort the text.
For example if you click on C7 then the data is sorted - but this cell is not a table header.
If you click on G26 then a message appears "runtime error 1004" - I'm really stuck!

https://www.box.com/s/8161b2f9bd8213e86ba3

Regards, Matt

 

[drsarao]

Hi again,

I wondered if you could just look at the sheet again as I cant seem to figure out
why the rows sort when you click on a childs name.
I would like to be able to just click on the table headers to sort the text.
For example if you click on C7 then the data is sorted - but this cell is not a table header.
If you click on G26 then a message appears "runtime error 1004" - I'm really stuck!


Regards, Matt

VoG had suggested to add "exit sub" at http://www.mrexcel.com/forum/showthread.php?641149-Adding-a-sort-button-to-a-table

I don't see that in your code.

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
[COLOR=#ff0000]If Target.Row <> 5 Then Exit Sub[/COLOR]
Static MySortType As Long
If MySortType = 0 Then
    MySortType = xlAscending
ElseIf MySortType = xlAscending Then
    MySortType = xlDescending
ElseIf MySortType = xlDescending Then
    MySortType = xlAscending
End If
Target.CurrentRegion.Sort key1:=Target, order1:=MySortType, Header:=xlYes
Cancel = True
End Sub