Creating

Hello All,
I have a worksheet of data with the following characteristics:
--column headers
--numeric and textual data
--a "frozen" pane
--some columns in which input is constrained by validation lists stored on 
other worksheets within the same Excel file

What I need to do is make it so that each of the main worksheet's column 
headers present my Excel-newbie end-users with a simple dropdown/pulldown 
that allows them to sort (ascending and descending) all the rows across all 
of the columns, i.e. across the frozen pane line, across the validation-list 
columns, etc. In an ideal world, users would be able to sort by multiple 
columns, i.e. by A then B then C, but I'll happily settle for sorting by one 
column.

For the life of me, I can't seem to figure this out. Thanks in advance for 
any help -- it's much appreciated.

Jack

0
10/20/2005 6:24:05 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
537 Views

Similar Articles

[PageSpeed] 30

How about this.  You put some rectangles over each of the header cells in row
1.  Make them invisible.  When the user clicks on one of those rectangles, your
data gets sorted by that column (click on the same rectangle, it sorts in the
opposite direction).

If that sounds promising...

Saved from a previous post...

I put rectangles over the headers (made the borders invisible) so that when you
clicked on the rectangle, it looked like you were clicking on the header.

Option Explicit
Sub setupOneTime()

    Dim myRng As Range
    Dim myCell As Range
    Dim curWks As Worksheet
    Dim myRect As Shape
    
    Set curWks = ActiveSheet
    
    With curWks
        '10 columns
        Set myRng = .Range("a1").Resize(1, 10)
        For Each myCell In myRng.Cells
            With myCell
                Set myRect = .Parent.Shapes.AddShape _
                                (Type:=msoShapeRectangle, _
                                Top:=.Top, Height:=.Height, _
                                Width:=.Width, Left:=.Left)
                End With
                With myRect
                    .OnAction = ThisWorkbook.Name & "!SortTable"
                    .Fill.Visible = False
                    .Line.Visible = False
                End With
        Next myCell
    End With
End Sub
Sub sortTable()

    Dim myTable As Range
    Dim myColToSort As Long
    Dim curWks As Worksheet
    Dim mySortOrder As Long
    Dim LastRow As Long
    
    Set curWks = ActiveSheet
    With curWks
        myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
        LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
        Set myTable = .Range("a1:a" & LastRow).Resize(, 10)
        If .Cells(myTable.Row + 1, myColToSort).Value _
          < .Cells(LastRow, myColToSort).Value Then
            mySortOrder = xlDescending
        Else
            mySortOrder = xlAscending
        End If
        myTable.Sort key1:=.Cells(myTable.Row, myColToSort), _
                        order1:=mySortOrder, _
                        header:=xlYes
    End With
    
End Sub

===
The setuponetime routine puts the rectangles in 10 columns A1:J1 with this:
        '10 columns
        Set myRng = .Range("a1").Resize(1, 10)

The sortTable routine sorts that same table based on the number of cells used in
column A (and 10 columns wide) with this line:

Set myTable = .Range("a1:a" & LastRow).Resize(, 10)

If you have more or less columns, adjust both .resize(,10) portions.

If you have to use a different column to get the last row, adjust this line:
        LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row


ActualSelf wrote:
> 
> Hello All,
> I have a worksheet of data with the following characteristics:
> --column headers
> --numeric and textual data
> --a "frozen" pane
> --some columns in which input is constrained by validation lists stored on
> other worksheets within the same Excel file
> 
> What I need to do is make it so that each of the main worksheet's column
> headers present my Excel-newbie end-users with a simple dropdown/pulldown
> that allows them to sort (ascending and descending) all the rows across all
> of the columns, i.e. across the frozen pane line, across the validation-list
> columns, etc. In an ideal world, users would be able to sort by multiple
> columns, i.e. by A then B then C, but I'll happily settle for sorting by one
> column.
> 
> For the life of me, I can't seem to figure this out. Thanks in advance for
> any help -- it's much appreciated.
> 
> Jack

-- 

Dave Peterson
0
petersod (12004)
10/20/2005 7:03:29 PM
Reply:

Similar Artilces:

Creating church directory by merge document
I am trying to create a 3-column directory of names and addresses, the data source is an xcell spread sheet. I have been able to enter codes but can find no way, at end of grouping to go to the next record. AND I can find no "if" statements to help organizing. I am doing it in Publisher. I have tried Word but ran into the same problems. FYI this is a church directory. Names, addresses, birthdates, etc. Thank you. Have you looked into using the catalog merge? I've made many church directories using the catalog merge. http://office.microsoft....

create user message
hi All I want to create a message box after the user presses the save button (anywhere). it looks like the CRM does not support this. the post action is not suitable since the client disconnects immediately after it posts the fields to the url... I was wondering if there is a "back door" do perform this... thanks, Ron Ron, Currently, there is no supported way to do this. You could look into modifying the underlying aspx page though to do something. Matt Parks ---------------------------------------- ---------------------------------------- On Wed, 12 May 2004 09:35:26 +020...

how can i create a leaflet in publisher
How do i crete a leaflet that folds, two folds, three pages, six counting the backs as well. and how do i change what page i look at. In Publisher 2000 it's; File New The Wizard comes up, select Brochures. -- Don Vancouver, USA "C" <C@discussions.microsoft.com> wrote in message news:1AA87D44-A572-4CA3-AB9D-79D46A699EFD@microsoft.com... > How do i crete a leaflet that folds, two folds, three pages, six counting > the > backs as well. > and how do i change what page i look at. > ...

how can I save a publisher created logo in picture format?
I've created a logo in MS Publisher. How can I save the logo in picture format so that it can be inserted into other programs? GEL <GEL@discussions.microsoft.com> was very recently heard to utter: > I've created a logo in MS Publisher. How can I save the logo in > picture format so that it can be inserted into other programs? What version of Publisher are you using? -- Ed Bennett - MVP Microsoft Publisher "Ed Bennett" wrote: > GEL <GEL@discussions.microsoft.com> was very recently heard to utter: > > I've created a logo in MS Publ...

Is it possible to create an internal "search box" feature
I work for a college and we would like to put a "search box" feature on all of the pages for internal use. Is this possible to do in Publisher? Any help would be appreciated. ...

Creating an email message in Outlook 2007
What does Outlook use as the editor when creating an email message, looks very much like Word. Options no longer offers Word as an alternative. Hello, yes, Outlook 2007 uses Word to render and create messages- There is no possibility to change anymore. Roman Brehm "PE" <pitsofearth@msn.com> wrote in message news:%23imSovkXHHA.4308@TK2MSFTNGP05.phx.gbl... > What does Outlook use as the editor when creating an email message, looks > very much like Word. Options no longer offers Word as an alternative. > > > > It is word. If you have the office suit...

HowTo for creating child window dialogs that can be used as child controls?
Hello, Can someone point to some docs or a HowTo for creating child window dialogs using the resource editor that can be used as child controls in a dialog window or child window? I'm not sure what dialog window styles, etc to use and how to 'wire' the child window to its parent. Basically I want to be able to layout a few 'placeholder child windows' on a dialog and insert my child windows at runtime. This if for an unmanaged C++ app using VS.Net 2003 and MFC. Thanks for any help. -Randy I believe the child dialog will also need the WS_EX_CONTROLPARENT style in ...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Script Create Mail Box
Is there a script I can run on an OU to create mailboxes in bulk and specify an Exchange Mail Store? I am in the process of creating a new Exchange server and I want to add users to an OU; run a script that will create the mailbox and assign it to an Exchange Storage Group based upon the OU. Does this sound possible? Yes it is absolutely possible although you'll have to find a script or right one yourself. An alternative might be to use a tool like ldifde to do a bulk import. Nue "Adam" <Adam@discussions.microsoft.com> wrote in message news:40D54FE3-CCF1-40C2-830...

How do I create a survey/questionnaire on Microsoft Off Publisher
I need to put a survey I have into .html format. Can I do that on publisher or do I need frontpage? Any suggestions where to start? File, Convert to Web Publication. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Creating Survey..." <Creating Survey...@discussions.microsoft.com> wrote in message news:FEB77FB7-F302-4483-ABB9-4E3816C52792@microsoft.com... >I need to put a survey I have into .html format. Can I do that on publisher > or do I need frontpage? Any suggestions where to start? Do I first creat...

How to create a Workflow .NET assembly that creates a CUSTOM ENTITY object?
Hi everybody! I need some help!! :) I created a custom entity on my CRM 3.0 Server, called "PosVenda". I also created some custom fields and a relationship with the Opportunity entity. It's working fine. But now, I need to create a workflow rule, that fires when an Opportunity is closed (Won), and creates a "PosVenda". I'm trying now to create a Workflow .NET assembly, to do this action. But the question is: How can I use CRM classes to create a new "PosVenda"? Thanks a lot for your help!! []'s Nando ...

Why can't I re-import a registry file created by RegSaveKey(...) under Win 9x?
Hello: I was trying to save a registry key to a file say, "mykey" with help of RegSaveKey(...) [not RegSaveKeyEx(...)]. The file can be re-imported with "Regedit" under WinXP if it is produced under Win XP. But for the same program running under Win 98/95, the files created under these OSs cannot be re-imported. These files cannot be re-imported under WinXP. Can anyone tell me why? The exerpted code is listed below: .... char szSubKey[_MAX_FNAME]; char FileName[_MAX_FNAME]; long rc = 0; HKEY hKey; sprintf(szSubKey, "Software\Mykey"); sprintf(FileName,...

How Do You Create A fill in form?
I am relatively new to the Office applications and need help fast! I am working with Office 2007 & need to create an application that can be filled out on line & submitted. I will be using the form on my website. Would I creat the application in Word or Publisher and how do you create the blanks to be filled in? Is there a generic form on line that can be altered to suit your needs? I would suggest using Word, not Publisher. http://office.microsoft.com/en-us/word/HA100307461033.aspx -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft...

Creating a Formula to Format Column automatically? #3
Can I record it as a macro or anything like that? I have to run it o five sheets. I've tried to conditional formatting, and it works only o one cell, unless I'm doing it wrong -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 not sure what you are trying to do but conditional formating can be coped and pasted. you have also conditional format an entire column/row by selected the en...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Create Hyperlink
Hi, when i right click on a cell to create hyperlink it doesn't appear. pls help How about Insert>Hyperlink? Available or not? Could be the workbook is Shared. Unshare it to get the feature back. Could be the worksheet is protected. In Excel 2002 or newer you can choose "Insert Hyperlinks" when protecting the sheet. Gord Dibben MS Excel MVP On Thu, 5 Apr 2007 02:22:03 -0700, Mohamed <Mohamed@discussions.microsoft.com> wrote: >Hi, >when i right click on a cell to create hyperlink it doesn't appear. >pls help ...

Create a font scheme??
I am creating a newsletter in Publisher 2003 and would like to create a font scheme, just like I created a colour scheme. Is this possible? In news:D201A5D0-A52F-43BD-A63F-8ACAE712C6C9@microsoft.com, IT Nightmare <anonymous@discussions.microsoft.com> posted: > I am creating a newsletter in Publisher 2003 and would like to create > a font scheme, just like I created a colour scheme. Is this > possible? Check out Ed's great tutorial on this: http://www.mvps.org/the_nerd/Publisher/FontScheme.htm -- Brian Kvalheim Microsoft Publisher MVP http://www.mvps.org/publisher This...

Creating Cases for MS CRM Users
We are using the software as a helpdesk for our IT department. Currently, we have employees setup as Contacts, but all employees are also CRM Users. We've been having issues with the auto-matching functionality because our Users and Contacts both use the same e-mail address. It would serve the system well to allow us to create cases where Customer = MS CRM User as opposed to limiting this to just Accounts or Contacts so we can eliminate the Contact records from our system completely. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions...

Error in creating a mailbox on exchange 5.5 says trust failed between primary and trusted domain
I have Exchange 5.5 setup on Windows 2K in a W2K3 domain. If I try and add a mailbox without a primary account assigned to it I get an Error that says The trust relationship between the primary and trusted domain failed. Now I do have several NT 4.0 domains that have a two-way trust between them and my 2k3 domain. If I create a new account or assign to an existing account I do not get the error. Any Ideas ? Sounds like things are working like they're supposed to. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Darryl Burnette&...

Creating & Exporting ASCII Fixed Length Records
I'm not sure you can do this in Excel but thought I would bounce off you guys... I want to create a data input form in Excel and then export it as an ASCII Fixed Length Record. Each field has a specified lenght of characters/bytes. First I thought of doing it like this... Name _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Address _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Account: _ _ _ _ _ _ _ _ _ _ But, when you highlight this and try to export, all the field will be the same length... I think? I could do it all on a single row maybe with titles over each cell and com...

How do I create a "floating" header in Excel so I can always see i
contact me at andy@boydrealestateinc.com Andy To have row 1 always in view as you scroll down. Select A2 and Window>Freeze Panes. Rule is: whatever is above and left of the selected cell will be frozen after you hit Window>Freeze Panes. i.e. select B2 to have Column A and Row 1 frozen. Gord Dibben Excel MVP On Wed, 16 Nov 2005 13:38:00 -0800, Andy Heal <Andy Heal@discussions.microsoft.com> wrote: >contact me at andy@boydrealestateinc.com ...

create a new user
I just created a new user in GP 8.0, assigned the user to a class, and company. I checked SQL Server and found the user is also created in database. But I got "There isn't a valid login record on the server for the user ID and password you entered." when I tried to login the new user. Do I need to do more to create a new user? Please help. Thanks. You need to have the SQL options Create Server Logons as well as Create Database Users turned on. This can be done through GPUtilities -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users For help lea...

How do I create columns in a catalog merge?
How do I re-arrange the merge fields into columns instead of rows? (Top to bottom - 3 columns per page) Resize the "catalog merge area" until it says it will repeat 3 across. Resize the merge area vertically so it says Area will repeat 1 down. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dcallaway" <dcallaway@discussions.microsoft.com> wrote in message news:56B87903-2B55-46F8-9A19-5089F9A1C2C4@microsoft.com... > How do I re-arrange the merge fields into columns instead of rows? (Top to > bottom...

Creating a chart with varying data value ranges
I have a chart that I want to show home price data with. The chart will have data that has very significantly varying data values. For example, one line graph will be 50-100, one will be 400,000 to 500,000, one will be 90% to 110%. I would like to show these line graphs on one chart. How to I make a chart with differing ranges on the x (vertical) axis? Take a bit of seutp work, but the end result here is impressive: http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html BTW, the vertical axis is the y-axis. -- Best Regards, Luke M "Anthony Blackburn&quo...

Create date
Hello I have a excel template that the user opens fills out and saves it by giving it a filename. The problem I'm having is there is a a date field that they need to enter in the date for that days information but they are not filling it in. Is there a function I can put in that cell that will put a date in it when the file is created? The file is saved first thing in the morning and is filled out and saved throughout the day until the next morning TIA David How about this? Function CreationDate() as String CreationDate = ActiveWorkbook.BuiltinDocumentProperties("Cr...