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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by selecting header cell?
You could add a clear rectangle that fills the header cell. Attach your sub to the rectangle. Or you could use the Worksheet_BeforeDoubleClick event to run the code. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Bassman62" wrote in message 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by selecting header cell?
Hi Bassman,
I would follow Jim's advice and use a shape's click event or the Worksheet's BeforeDoubleClick event. If, however, as you indicate, you wish to launch the sort operation in response to the selection of a header cell, try: '========== Private Sub Worksheet_SelectionChange _ (ByVal Target As Range) Dim Rng As Range Set Rng = Me.Range("headers") If Not Intersect(Rng, Target) Is Nothing Then Call mySortMacro 'CommandButton1_Click End If End Sub '<<========== This is event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab | Select 'View Code' from the menu Paste the code Alt-F11 to return to Excel. --- Regards. Norman "Bassman62" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by selecting header cell?
Thanks to you both.
I liked the "Worksheet_SelectionChange ..." method Works like a charm. Thanks again! "Norman Jones" wrote: Hi Bassman, I would follow Jim's advice and use a shape's click event or the Worksheet's BeforeDoubleClick event. If, however, as you indicate, you wish to launch the sort operation in response to the selection of a header cell, try: '========== Private Sub Worksheet_SelectionChange _ (ByVal Target As Range) Dim Rng As Range Set Rng = Me.Range("headers") If Not Intersect(Rng, Target) Is Nothing Then Call mySortMacro 'CommandButton1_Click End If End Sub '<<========== This is event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab | Select 'View Code' from the menu Paste the code Alt-F11 to return to Excel. --- Regards. Norman "Bassman62" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by selecting header cell?
Click_Sort example works on columns A thru D, change the range of columns to
suit: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:D1")) Is Nothing Then With Target If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then .Value = .Value & " t" .Characters(Len(.Value), 1).Font.Name = "Marlett" End If If Right(.Value, 1) = "t" Then .EntireColumn.Sort Key1:=.Offset(1, 0), _ Order1:=xlAscending, _ HEADER:=xlYes .Value = Left(.Value, Len(.Value) - 1) & "u" .Characters(Len(.Value), 1).Font.Name = "Marlett" Else .EntireColumn.Sort Key1:=.Offset(1, 0), _ Order1:=xlDescending, _ HEADER:=xlYes .Value = Left(.Value, Len(.Value) - 1) & "t" .Characters(Len(.Value), 1).Font.Name = "Marlett" End If .Offset(1, 0).Activate End With End If ws_exit: Application.EnableEvents = True End Sub Mike F "Bassman62" wrote in message ... 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 |
Reply |
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) |