Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by selecting header cell?
Using Excel 2007 on WinXP
How can I sort a list simply by selecting one of the header cells? I have a list that includes unlocked cells and locked cells with formulas. The file is used by several users and the sheet is protected to prevent changes to the formulas. However, the users are required to sort the list after adding to it and this is not allowed for locked cells on a protected sheet. I have a macro that unprotects the sheet, sorts the list keying on the active cell, then protects the sheet. It requires the user to select a cell within the list then click on a control button to activate the macro. How can I accomplish the same operation not with a command button but by simply selecting one of the header cells? Thanks much. Below is the code I have: Private Sub CommandButton1_Click() ' Sort list using the column of the active cell as the sort key Dim SortKey1 As String SortKey1 = Range("headers").Columns(ActiveCell.Column - (Range("headers").Column - 1)) If Not Intersect(ActiveCell, Range("JobList")) Is Nothing Then ActiveSheet.Unprotect ' Unprotect the active sheet Range("JobList").Sort Key1:=SortKey1, Order1:=xlAscending, Header:=xlYes ' Protect the active sheet without a password ActiveSheet.Protect DrawingObjects:=False, _ Contents:=True, Scenarios:=False End If End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a list in 1 cell by selecting an item from another list in | Excel Discussion (Misc queries) | |||
Fill a cell by selecting a value from a list | Excel Worksheet Functions | |||
select list by selecting a cell | Excel Worksheet Functions | |||
Sort - Header:=xlGuess vs Header:=xlNo | Excel Programming | |||
sort columns within a merged cell header | Excel Discussion (Misc queries) |