Help with school data sheet - debugging

mj_bowen

Board Regular
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)

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

Matt

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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"

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,

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

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

thanks again,

Matt

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

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

I don't see that in your code.

Try this:
Code:
``````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
Cancel = True
End Sub``````

Replies
2
Views
182
Replies
9
Views
593
Replies
1
Views
169
Replies
5
Views
473
Replies
0
Views
371

1,203,752
Messages
6,057,151
Members
444,908
Latest member
Jayrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back