Sort table with one click on header?

[Guest]

I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[Gord Dibben]

Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP

I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[Gord Dibben]

Colby

You want easy or safe?

A one-click sort could be possible through event code but the first time your
columns and rows got all mucked up due to Excel making a wrong guess at the used
range because a user inserted a blank column and then sorts and saves, you will
be some upset.

The only real safe way to sort and keep your matrix intact is to select the
range of columns first then do your sort.


Gord

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP

I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[Guest]

Gord,

Can you point me toward event code for this? My users won't be changing the
table -- they'll be counting on me for that.

I appreciate the warning, though; Murphy's Law is attracted to situations
like this.

Thanks
Colby

Colby

You want easy or safe?

A one-click sort could be possible through event code but the first time your
columns and rows got all mucked up due to Excel making a wrong guess at the used
range because a user inserted a blank column and then sorts and saves, you will
be some upset.

The only real safe way to sort and keep your matrix intact is to select the
range of columns first then do your sort.


Gord

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP


I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[Debra Dalgleish]

Dave Peterson has sample code in a workbook that you can download here:

http://www.contextures.com/xlSort02.html

Gord,

Can you point me toward event code for this? My users won't be changing the
table -- they'll be counting on me for that.

I appreciate the warning, though; Murphy's Law is attracted to situations
like this.

Thanks
Colby



:

Colby

You want easy or safe?

A one-click sort could be possible through event code but the first time your
columns and rows got all mucked up due to Excel making a wrong guess at the used
range because a user inserted a blank column and then sorts and saves, you will
be some upset.

The only real safe way to sort and keep your matrix intact is to select the
range of columns first then do your sort.


Gord

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

:


Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP


I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[Gord Dibben]

Thanks Debra

Saved mesearching for the URL.

I knew Dave had it at your site somewhere.

Gord

Dave Peterson has sample code in a workbook that you can download here:

http://www.contextures.com/xlSort02.html

Gord,

Can you point me toward event code for this? My users won't be changing the
table -- they'll be counting on me for that.

I appreciate the warning, though; Murphy's Law is attracted to situations
like this.

Thanks
Colby



:

Colby

You want easy or safe?

A one-click sort could be possible through event code but the first time your
columns and rows got all mucked up due to Excel making a wrong guess at the used
range because a user inserted a blank column and then sorts and saves, you will
be some upset.

The only real safe way to sort and keep your matrix intact is to select the
range of columns first then do your sort.


Gord

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

:


Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP


I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[Debra Dalgleish]

You're welcome! On a good day, I can remember some of the things that
are on my site. <g>

Thanks Debra

Saved mesearching for the URL.

I knew Dave had it at your site somewhere.

Gord

Dave Peterson has sample code in a workbook that you can download here:

http://www.contextures.com/xlSort02.html

Gord,

Can you point me toward event code for this? My users won't be changing the
table -- they'll be counting on me for that.

I appreciate the warning, though; Murphy's Law is attracted to situations
like this.

Thanks
Colby



:



Colby

You want easy or safe?

A one-click sort could be possible through event code but the first time your
columns and rows got all mucked up due to Excel making a wrong guess at the used
range because a user inserted a blank column and then sorts and saves, you will
be some upset.

The only real safe way to sort and keep your matrix intact is to select the
range of columns first then do your sort.


Gord

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

:



Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP


I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.

 

[JB]

Sort on Double-Clic:

http://boisgontierj.free.fr/fichiers/Tri/TriDblClicColonne.xls

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Set titre = Target.CurrentRegion.Resize(1,
Target.CurrentRegion.Columns.Count)
If Not Intersect(titre, Target) Is Nothing Then
OrdreTri = IIf(Target.Interior.ColorIndex = 3, xlDescending,
xlAscending)
Target.CurrentRegion.Sort Key1:=Cells(1, Target.Column),
Order1:=OrdreTri, Header:=xlGuess
m = IIf(Target.Interior.ColorIndex = 3, 4, 3)
titre.Interior.ColorIndex = 44
Target.Interior.ColorIndex = m
End If
Cancel = True
End Sub

JB

 

[Guest]

Debra and Gord,

Thanks very much for your help on this.
Cheers
Colby

Thanks Debra

Saved mesearching for the URL.

I knew Dave had it at your site somewhere.

Gord

Dave Peterson has sample code in a workbook that you can download here:

http://www.contextures.com/xlSort02.html

Gord,

Can you point me toward event code for this? My users won't be changing the
table -- they'll be counting on me for that.

I appreciate the warning, though; Murphy's Law is attracted to situations
like this.

Thanks
Colby



:


Colby

You want easy or safe?

A one-click sort could be possible through event code but the first time your
columns and rows got all mucked up due to Excel making a wrong guess at the used
range because a user inserted a blank column and then sorts and saves, you will
be some upset.

The only real safe way to sort and keep your matrix intact is to select the
range of columns first then do your sort.


Gord

Hi Gord,

Thanks for the quick response. I was hoping there was a way to set it up so
users could just click on the column heading *without* having to go to
Data>Sort. The Create List pull-down would be OK if I could eliminate the
other filter options that appear there. [The fewer options there are for my
users, the better ]

Colby

:


Colby

If you have created a List select top cell in a column(not the column header)
and Data>Sort

Or don't create a list and just click on a header and Data>Sort.


Gord Dibben MS Excel MVP


I want to sort a table in Excel 2003 by clicking on any of the column headers
-- like in an email window. The "Create List" feature is almost what I need,
but I want to show only "Sort Ascending" and "Sort Descending" on the
pull-downs. [It's unfortunate that you have to scroll *up* to see the Sort
options in those pull-downs.]

Is there a way to eliminate or mask the other filtering options that appear
in the List pull-downs? Or is there another way I can sort by column headers
with one click?

Thanks for any ideas on this.