#1   Report Post  
Posted to microsoft.public.excel.misc
DCSwearingen
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Limiting ranges when building charts in vba Benoit Excel Discussion (Misc queries) 0 March 21st 06 12:07 AM
Select rows and sort based on type Sarah Excel Discussion (Misc queries) 0 October 11th 05 05:06 PM
Sort Macro: Utilizing ComboBox Selection for Key1 through 3 Ranges buddhapenguin Excel Discussion (Misc queries) 0 May 13th 05 03:01 PM
"-" ignored in sort Mike H Excel Discussion (Misc queries) 8 January 2nd 05 08:48 AM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 02:33 PM.

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

About Us

"It's about Microsoft Excel"