David
You will need a sheet event macro. The macro below will work with the
8th column being Column H, headers in row 1, and data starting in row 2.
You will need to unlock all the cells in the 8 columns for as many rows as
you think you might have data in the future. If you have data in only the 8
columns in the sheet, it would be simpler to unlock all the cells in the
sheet.
If you have some data already in those 8 columns that you want to "lock",
simply select all those cells and lock them. Be aware that
locking/unlocking a cell does nothing, by itself, to protect that cell. The
sheet must be protected as well. With the sheet protected, access to change
the contents of a cell will be denied ONLY if that cell is locked.
You need to protect the sheet.
Once you have done all the above, install the macro below.
The macro below will do the following:
The macro will fire with a change to the contents of any cell in the sheet.
If the changed cell (the "Target" cell) is not in Column H, the macro will
do nothing.
If the target cell is in Column H, the macro will:
Lock the cells in that row in all 8 columns.
Sort all the data in those 8 columns by Column A.
Done.
Note that this macro must be placed in the sheet module of that one specific
sheet. To access that module, right-click on the sheet tab, and select View
Code. Paste this macro into the displayed module. "X" out of the module to
return to your sheet. Post back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing And _
Target.Row > 1 Then
Dim RngToSort As Range
ActiveSheet.Unprotect
Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Locked = True
Set RngToSort = Range("A1", Range("A" &
Rows.Count).End(xlUp).Offset(, 7))
Application.EnableEvents = False
RngToSort.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
ActiveSheet.Protect
End If
End Sub
I have a worksheet in which I enter data under 8 titled columns. After
entering info on the 8th and final column, I would like for excel to sort by
column 1, which is the date, and also lock the row, say row 24, preventing
mistakes on previously entered transactions. If anyone knows how to help, I
would definitely appreciate it.
David
