A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Sort table with one click on header?



 
 
Thread Tools Display Modes
  #1  
Old March 3rd 07, 11:31 PM posted to microsoft.public.excel.misc
Colby
external usenet poster
 
Posts: 10
Default Sort table with one click on header?

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.
Ads
  #2  
Old March 3rd 07, 11:48 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default Sort table with one click on header?

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


On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
wrote:

>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.


  #3  
Old March 4th 07, 12:00 AM posted to microsoft.public.excel.misc
Colby
external usenet poster
 
Posts: 10
Default Sort table with one click on header?

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

"Gord Dibben" wrote:

> 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
>
>
> On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
> wrote:
>
> >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.

>
>

  #4  
Old March 4th 07, 12:19 AM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default Sort table with one click on header?

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

On Sat, 3 Mar 2007 16:00:06 -0800, Colby >
wrote:

>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
>
>"Gord Dibben" wrote:
>
>> 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
>>
>>
>> On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
>> wrote:
>>
>> >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.

>>
>>


  #5  
Old March 4th 07, 01:01 AM posted to microsoft.public.excel.misc
Colby
external usenet poster
 
Posts: 10
Default Sort table with one click on header?

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



"Gord Dibben" wrote:

> 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
>
> On Sat, 3 Mar 2007 16:00:06 -0800, Colby >
> wrote:
>
> >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
> >
> >"Gord Dibben" wrote:
> >
> >> 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
> >>
> >>
> >> On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
> >> wrote:
> >>
> >> >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.
> >>
> >>

>
>

  #6  
Old March 4th 07, 01:19 AM posted to microsoft.public.excel.misc
Debra Dalgleish
external usenet poster
 
Posts: 2,979
Default Sort table with one click on header?

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

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


Colby wrote:
> 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
>
>
>
> "Gord Dibben" wrote:
>
>
>>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
>>
>>On Sat, 3 Mar 2007 16:00:06 -0800, Colby >
>>wrote:
>>
>>
>>>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
>>>
>>>"Gord Dibben" wrote:
>>>
>>>
>>>>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
>>>>
>>>>
>>>>On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
>>>>wrote:
>>>>
>>>>
>>>>>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
Contextures
http://www.contextures.com/tiptech.html

  #7  
Old March 4th 07, 02:35 AM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default Sort table with one click on header?

Thanks Debra

Saved mesearching for the URL.

I knew Dave had it at your site somewhere.

Gord

On Sat, 03 Mar 2007 20:19:58 -0500, Debra Dalgleish >
wrote:

>Dave Peterson has sample code in a workbook that you can download he
>
> http://www.contextures.com/xlSort02.html
>
>
>Colby wrote:
>> 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
>>
>>
>>
>> "Gord Dibben" wrote:
>>
>>
>>>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
>>>
>>>On Sat, 3 Mar 2007 16:00:06 -0800, Colby >
>>>wrote:
>>>
>>>
>>>>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
>>>>
>>>>"Gord Dibben" wrote:
>>>>
>>>>
>>>>>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
>>>>>
>>>>>
>>>>>On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
>>>>>wrote:
>>>>>
>>>>>
>>>>>>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.
>>>>>
>>>>>
>>>


  #8  
Old March 4th 07, 04:38 AM posted to microsoft.public.excel.misc
Debra Dalgleish
external usenet poster
 
Posts: 2,979
Default Sort table with one click on header?

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

Gord Dibben wrote:
> Thanks Debra
>
> Saved mesearching for the URL.
>
> I knew Dave had it at your site somewhere.
>
> Gord
>
> On Sat, 03 Mar 2007 20:19:58 -0500, Debra Dalgleish >
> wrote:
>
>
>>Dave Peterson has sample code in a workbook that you can download he
>>
>> http://www.contextures.com/xlSort02.html
>>
>>
>>Colby wrote:
>>
>>>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
>>>
>>>
>>>
>>>"Gord Dibben" wrote:
>>>
>>>
>>>
>>>>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
>>>>
>>>>On Sat, 3 Mar 2007 16:00:06 -0800, Colby >
>>>>wrote:
>>>>
>>>>
>>>>
>>>>>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
>>>>>
>>>>>"Gord Dibben" wrote:
>>>>>
>>>>>
>>>>>
>>>>>>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
>>>>>>
>>>>>>
>>>>>>On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
>>>>>>wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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
Contextures
http://www.contextures.com/tiptech.html

  #9  
Old March 4th 07, 06:54 AM posted to microsoft.public.excel.misc
JB
external usenet poster
 
Posts: 115
Default Sort table with one click on header?

Sort on Double-Clic:

http://boisgontierj.free.fr/fichiers...licColonne.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
On 4 mar, 00:31, Colby > wrote:
> 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.



  #10  
Old March 4th 07, 07:21 AM posted to microsoft.public.excel.misc
Colby
external usenet poster
 
Posts: 10
Default Sort table with one click on header?

Debra and Gord,

Thanks very much for your help on this.
Cheers
Colby



"Gord Dibben" wrote:

> Thanks Debra
>
> Saved mesearching for the URL.
>
> I knew Dave had it at your site somewhere.
>
> Gord
>
> On Sat, 03 Mar 2007 20:19:58 -0500, Debra Dalgleish >
> wrote:
>
> >Dave Peterson has sample code in a workbook that you can download he
> >
> > http://www.contextures.com/xlSort02.html
> >
> >
> >Colby wrote:
> >> 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
> >>
> >>
> >>
> >> "Gord Dibben" wrote:
> >>
> >>
> >>>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
> >>>
> >>>On Sat, 3 Mar 2007 16:00:06 -0800, Colby >
> >>>wrote:
> >>>
> >>>
> >>>>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
> >>>>
> >>>>"Gord Dibben" wrote:
> >>>>
> >>>>
> >>>>>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
> >>>>>
> >>>>>
> >>>>>On Sat, 3 Mar 2007 15:31:05 -0800, Colby >
> >>>>>wrote:
> >>>>>
> >>>>>
> >>>>>>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.
> >>>>>
> >>>>>
> >>>

>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
get sort to recognize header row widman Excel Discussion (Misc queries) 3 October 25th 06 04:14 AM
sort, 2 row header Kath Excel Worksheet Functions 5 April 14th 06 11:19 PM
how can I sort with header not on row 1 tryer Excel Discussion (Misc queries) 4 March 31st 06 09:28 PM
SORT Header Row(s) Karen Excel Discussion (Misc queries) 1 January 4th 06 01:33 AM
Click on graph bar to execute a double-click in a pivot table cell [email protected] Charts and Charting in Excel 4 August 3rd 05 01:37 AM


All times are GMT +1. The time now is 10:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.