Limiting Sort Ranges

[DCSwearingen]

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 ' This is the row with buttons
ExitMessage
Exit Sub
Case 2 ' This is the row with the headers
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
End Sub

However if someone selects a single cell and runs this macro, the header row is included as data in the sort and not as a header.

How can I ensure this does not happen, similar to the select case if they happen to select the top two rows...

[Dave Peterson]

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