Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Ah-ha, okay I'll see if I can get that done. This would be a cell somewhere
out of the way of the data ranges, I presume. Correct! This gives the user easy access for editing. This can be a hidden column that requires dialog access (InputBox perhaps). Otherwise, if you want to restrict editing or keep the sheet 'clean' then use a defined name, which optionally can be edited via a dialog. You could even precede the sorting with a userform that informs the user that cols need sorting, displaying a list of col labels and their sort order. The MsgBox can ask if the sort is to be done with current settings or solicit new settings. If new settings are entered then update the named range (or defined name's RefersTo) before calling SortCols(). Just some food for thought if you want to make the project a bit more robust! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 10:41:04 PM UTC-7, GS wrote:
Ah-ha, okay I'll see if I can get that done. This would be a cell somewhere out of the way of the data ranges, I presume. Correct! This gives the user easy access for editing. This can be a hidden column that requires dialog access (InputBox perhaps). Otherwise, if you want to restrict editing or keep the sheet 'clean' then use a defined name, which optionally can be edited via a dialog. You could even precede the sorting with a userform that informs the user that cols need sorting, displaying a list of col labels and their sort order. The MsgBox can ask if the sort is to be done with current settings or solicit new settings. If new settings are entered then update the named range (or defined name's RefersTo) before calling SortCols(). Just some food for thought if you want to make the project a bit more robust! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion One hurdle to overcome before I play with that last suggestion, which by the way seems pretty keen. Got the cells named for four sheets, in the Name Manager looks like this: Name SortCriteria Value "e,f:g,h" Refers To =Sheet2!$XFD$1 Scope Sheet2 (Other three are same-same relative to their sheet) When I run the code (by selecting a sheet) I get a Compile Error ByRef argument Type Mismatch. The vSortCriteria (last word in this next line is blue highlighted) Which is the last line in the sheet activate code. If Not vSortCriteria = "" Then Call SortCols(Sh, vSortCriteria) Did I name the sheet cell incorrectly or is this something else? Howard |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Ah! No.., remove the $ symbol from the associated arg in Sub SortCols.
In fact, you can remove the ByVal part too since we only ref the var. Also change these lines in the SortCols() sub... If vSortCriteria(0) = Empty Then _ bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU If vSortCriteria(1) = Empty Then _ bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL ...so we won't use the LBound/UBound properties but ref the appropriate element by index instead. Note that omitting the coloon in the string will cause the sub to end after checking the elements for Empty, meaning no sort takes place. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Here's what I have now where I think I followed you instructions... I get the error with this entire sub heading blued out. Private Sub Workbook_SheetActivate(Sh As Object) "procedure declaration does not match description of event or procedure having same name" Private Sub Workbook_SheetActivate(Sh As Object) 'Sub xx() '//bno = maybe a msgbox .. sort or not sort? vbno = exit sub Dim vSortCriteria On Error Resume Next '//if name doesn't exist vSortCriteria = Sh.Range("SortCriteria").Value If Not vSortCriteria = "" Then Call SortCols(Sh, vSortCriteria) End Sub Sub SortCols(Wks As Worksheet, SortCriteria) ' Sorts individual specified cols ' Args: Wks The worksheet to be sorted ' SortCriteria Delimited string of col labels ' Not case sensitive ' **Note that SortCriteria is multi-delimited ' where sort order is delimited by a colon, ' and col labels by a comma. Left side of colon ' gets sorted ascending; right side descending. ' Examples: sort ascending only: "a,b,c,d,e:" ' sort descending only: ":a,b,c,d,e" ' sort both: "a,b,c:d,e" Dim vSortCriteria, vCols, vSortOrder, v, bOrderBoth As Boolean 'Assume both sort orders bOrderBoth = True 'Determine sort order vSortCriteria = Split(SortCriteria, ":") If vSortCriteria(0) = Empty Then _ bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU If vSortCriteria(1) = Empty Then _ bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL SortL: If bOrderBoth Then vSortOrder = xlAscending For Each v In Split(vSortCriteria(0), ",") Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _ Order1:=vSortOrder, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next 'v If Not bOrderBoth Then Exit Sub SortU: If bOrderBoth Then vSortOrder = xlDescending For Each v In Split(vSortCriteria(1), ",") Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _ Order1:=vSortOrder, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next 'v End Sub |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
I'm not getting any errors! For clarity...
In ThisWorkbook: Option Explicit Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim vSortCriteria On Error Resume Next '//if name doesn't exist vSortCriteria = Sh.Range("SortCriteria").Value If Not vSortCriteria = Empty Then Call SortCols(Sh, vSortCriteria) End Sub In a standard module: Option Explicit Sub SortCols(Wks As Worksheet, SortCriteria) ' Sorts individual specified cols ' Args: Wks The worksheet to be sorted ' SortCriteria Delimited string of col labels ' Not case sensitive ' **Note that SortCriteria is multi-delimited ' where sort order is delimited by a colon, ' and col labels by a comma. Left side of colon ' gets sorted ascending; right side descending. ' Examples: sort ascending only: "a,b,c,d,e:" ' sort descending only: ":a,b,c,d,e" ' sort both: "a,b,c:d,e" Dim vSortCriteria, vCols, vSortOrder, v, bOrderBoth As Boolean 'Assume both sort orders bOrderBoth = True 'Determine sort order vSortCriteria = Split(SortCriteria, ":") If vSortCriteria(0) = Empty Then _ bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU If vSortCriteria(1) = Empty Then _ bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL SortL: If bOrderBoth Then vSortOrder = xlAscending For Each v In Split(vSortCriteria(0), ",") Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _ Order1:=vSortOrder, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next 'v If Not bOrderBoth Then Exit Sub SortU: If bOrderBoth Then vSortOrder = xlDescending For Each v In Split(vSortCriteria(1), ",") Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _ Order1:=vSortOrder, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next 'v End Sub -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
IF you care to, here is a link to my workbook
Some reason it just wont fly for me. No errors and no sort. All sheets are set up the same Sort orders are in cell XFD1 https://www.dropbox.com/s/b0jdyo6kj5...p%20Box.xls m Howard |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Wed, 7 Aug 2013 03:22:19 -0700 (PDT) schrieb Howard: Sort orders are in cell XFD1 delete the quotes at the sort orders https://www.dropbox.com/s/b0jdyo6kj5...p%20Box.xls m vSortCriteria is in workbook event and in module1. So you have to declare it PUBLIC at the start of module1 Option Explicit Public vSortCriteria As Variant Sub SortCols(Wks As Worksheet, SortCriteria) ' Sorts individual specified cols ' Args: Wks The worksheet to be sorted ' SortCriteria Delimited string of col labels ' Not case sensitive ' **Note that SortCriteria is multi-delimited ' where sort order is delimited by a colon, ' and col labels by a comma. Left side of colon ' gets sorted ascending; right side descending. ' Examples: sort ascending only: "a,b,c,d,e:" ' sort descending only: ":a,b,c,d,e" ' sort both: "a,b,c:d,e" Dim vCols, vSortOrder, v, bOrderBoth As Boolean etc. etc. etc. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
vSortCriteria is in workbook event and in module1. So you have to
declare it PUBLIC at the start of module1 That's not true, IMO! Doing so would require resetting it before or after each time the event fires. As is, it self initializes to Empty and remains so until populated with data from Range("SortCriteria"). IOW, it serves as a test mechanism in the event procedure and on success it passes its string value to SortCols(). It appears again in SortCols in a different context because of its intended use. The fact that it happens to be the same name as the one in the event is purely by attrition. IMO, there's nothing wrong with your suggestion in context of a project! My intent for the sort routine is that it be generic so it can be reused without dependancies. A global var is a dependancy in the context of your suggestion! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
IF you care to, here is a link to my workbook
Some reason it just wont fly for me. No errors and no sort. All sheets are set up the same Sort orders are in cell XFD1 https://www.dropbox.com/s/b0jdyo6kj5...p%20Box.xls m Howard Ok.., as Claus suggests, remove the quotes from your SortCriteria ranges. (I'm not sure why they're there to begin with!) -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Ok.., as Claus suggests, remove the quotes from your SortCriteria ranges. (I'm not sure why they're there to begin with!) Removed the quotes, WORKS FINE!! The quotes were there due to my error. Saw quotes in the narrative some where and made a wrong assumption. Sorry for all the fuss it has caused. I consider this as a done deal, never thought it would evolve to be the very versatile version that it is. I'll have to parse this thread and take a look at your suggestion if inputbox's for column sort orders etc. Many thanks to you and Claus for making it happen. Where ever the code goes from me, it will have both your names attached. I often say something like "Thanks to Garry/Claus of MS Excel Forum". Hope you don't mind. Regards, Howard |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
I don't mind being mentioned as code source credit. I appreciate the
feedback, though, and glad you got it working. I figured the issue was the very late hour you were still going at it. Happens to me more than I can count. So then.., you would have no problem adding SortRows() to your arsenal of reusable procedures by simply modifying the SortCols() routine! Right?? -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#52
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Ok.., here ya go!
Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim vSortCriteria On Error Resume Next '//if name doesn't exist vSortCriteria = Sh.Range("SortCriteria").Value ' If Not vSortCriteria = Empty Then Call SortCols(Sh, vSortCriteria) If Not vSortCriteria = Empty Then Call SortRows(Sh, vSortCriteria) End Sub Sub SortRows(Wks As Worksheet, SortCriteria) ' Sorts individual specified rows ' Args: Wks The worksheet to be sorted ' SortCriteria Delimited string of row nums ' **Note that SortCriteria is multi-delimited ' where sort order is delimited by a colon, ' and row nums by a comma. Left side of colon ' gets sorted ascending; right side descending. ' Examples: sort ascending only: "2,3,4,5:" ' sort descending only: ":2,3,4,5" ' sort both: "2,3:4,5" Dim vSortCriteria, vSortOrder, v, bOrderBoth As Boolean 'Assume both sort orders bOrderBoth = True 'Determine sort order vSortCriteria = Split(SortCriteria, ":") If vSortCriteria(0) = Empty Then _ bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU If vSortCriteria(1) = Empty Then _ bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL SortL: If bOrderBoth Then vSortOrder = xlAscending For Each v In Split(vSortCriteria(0), ",") Wks.Rows(v).Sort Key1:=Wks.Cells(v, 1), _ Order1:=vSortOrder, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next 'v If Not bOrderBoth Then Exit Sub SortU: If bOrderBoth Then vSortOrder = xlDescending For Each v In Split(vSortCriteria(1), ",") Wks.Rows(v).Sort Key1:=Wks.Cells(v, 1), _ Order1:=vSortOrder, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next 'v End Sub All that needed is a mechanism to store both row/col sort criteria where both might be used on the same sheet. I'd go with using defined names (SortCriteriaR, SortCriteriaC) or a row above the data area where the cols used are also outside the data area. Using defined names avoids col.count issues when running later files in earlier apps. The approach to reading/writing these is a bit more complex but trivial nevertheless. This is where using a dialog to manage both would shine!<g -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#53
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Wednesday, August 7, 2013 11:20:01 AM UTC-7, GS wrote:
I don't mind being mentioned as code source credit. I appreciate the feedback, though, and glad you got it working. I figured the issue was the very late hour you were still going at it. Happens to me more than I can count. So then.., you would have no problem adding SortRows() to your arsenal of reusable procedures by simply modifying the SortCols() routine! Right?? Strange of you to mention that. I just minutes ago poster to the OP with the just finished workbook link and did indeed make a note that it does not sort rows. I occurred to me, but I felt compelled to "let the dust settle" before mentioning it. It really does make good sense to be able to do both, I think. And within the same code you have just written. Yes, indeed. Howard |
#54
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Wednesday, August 7, 2013 11:20:01 AM UTC-7, GS wrote:
I don't mind being mentioned as code source credit. I appreciate the feedback, though, and glad you got it working. I figured the issue was the very late hour you were still going at it. Happens to me more than I can count. So then.., you would have no problem adding SortRows() to your arsenal of reusable procedures by simply modifying the SortCols() routine! Right?? Strange of you to mention that. I just minutes ago poster to the OP with the just finished workbook link and did indeed make a note that it does not sort rows. I occurred to me, but I felt compelled to "let the dust settle" before mentioning it. It really does make good sense to be able to do both, I think. And within the same code you have just written. Yes, indeed. Howard My thoughts are to load a userform in the event code that depicts rows (if any) and/or cols (if any) with existing sort criteria for both already loaded into textboxes for editing. This could also be used as a preferences/settings/options dialog with that data stored on a hidden sheet. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#55
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
My thoughts are to load a userform in the event code that depicts rows
(if any) and/or cols (if any) with existing sort criteria for both already loaded into textboxes for editing. This could also be used as a preferences/settings/options dialog with that data stored on a hidden sheet. Garry Anxious to see it. Looks like new ground again for me. Userform/textbox stuff. Done a little but largely a mystery to me. Howard |
#56
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
My thoughts are to load a userform in the event code that depicts rows
(if any) and/or cols (if any) with existing sort criteria for both already loaded into textboxes for editing. This could also be used as a preferences/settings/options dialog with that data stored on a hidden sheet. Garry Anxious to see it. Looks like new ground again for me. Userform/textbox stuff. Done a little but largely a mystery to me. Howard I've already started! I'll email you the project when it's done but don't be in a rush because it's happening in my spare time... -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#57
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
You bet, Thanks. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Sorting Technique | Excel Discussion (Misc queries) | |||
Data sorting, please can anyone advise on the best function to use? | Excel Discussion (Misc queries) | |||
Which sorting technique does Excel use in the sort function? | Excel Discussion (Misc queries) | |||
Which sorting technique does Excel use in the sort function? | Excel Programming | |||
Best Technique to clone worksheet | Excel Programming |