Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limiting Sort Ranges
We have several spreadsheets in which we track production line downtime. (I would prefer to do this in Access, and we even paid a small fee to have a database created to do it, but people are so use to Excel that implementing the Access application proved to be impractical) Operators like to sort the rows in this, however they are constantly ending up with the header row being sorted as data instead of as the header. What I have done is to protect the spreadsheet, create a startup macro that unprotects & brings up a data form. Once the data form is closed the macro then protects the sheet again. They have a button that will run the startup marco again if they need to enbter additional data (or even edit existing data by scrolling through the records). I have a macro that will sort for them: Sub SortSelection() Dim myRow As Single myRow = Selection.Row Select Case myRow Case 1 ExitMessage Exit Sub Case 2 ExitMessage Exit Sub End Select ActiveSheet.UnProtect (myPW) Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _ ("B2"), Order2:=xlAscending, _ Key3:=Range("H2"), Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Range("A4").Select Selection.End(xlDown).Select ActiveSheet.Protect password:=myPW P2Down = True End Sub -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=550138 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limiting Sort Ranges
How about making it so that the users can click on the header for that column
and sort by that column? You'll have to add the unprotect and protect code, but if you want to try it, visit Debra Dalgleish's site: http://contextures.com/xlSort02.html DCSwearingen wrote: We have several spreadsheets in which we track production line downtime. (I would prefer to do this in Access, and we even paid a small fee to have a database created to do it, but people are so use to Excel that implementing the Access application proved to be impractical) Operators like to sort the rows in this, however they are constantly ending up with the header row being sorted as data instead of as the header. What I have done is to protect the spreadsheet, create a startup macro that unprotects & brings up a data form. Once the data form is closed the macro then protects the sheet again. They have a button that will run the startup marco again if they need to enbter additional data (or even edit existing data by scrolling through the records). I have a macro that will sort for them: Sub SortSelection() Dim myRow As Single myRow = Selection.Row Select Case myRow Case 1 ExitMessage Exit Sub Case 2 ExitMessage Exit Sub End Select ActiveSheet.UnProtect (myPW) Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _ ("B2"), Order2:=xlAscending, _ Key3:=Range("H2"), Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Range("A4").Select Selection.End(xlDown).Select ActiveSheet.Protect password:=myPW P2Down = True End Sub -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=550138 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limiting ranges when building charts in vba | Excel Discussion (Misc queries) | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
Sort Macro: Utilizing ComboBox Selection for Key1 through 3 Ranges | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |