SORTING ISSUE: PLEASE - I need another set of eyes!!

[lhasha]

I have a sheet that has four columns concerned in this issue.

Column B = A switch (See code below)
Column C = Employee ID
Column D = Employee Name
Column E = Pay Rate

I have three buttons (one at the top of each column C thru E). Each calls a routine to sort by the relative column. Each routine sorts columns B thru E. This means that all columns are in the sort but each sort is according to the column selected.

Important to note, ALL sorts work out fine...here is the problem.

Please review the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B6:B355")) Is Nothing Then
If Target.Value = "X" Then
Target.Interior.ColorIndex = 15
Target.Value = ""
Range(Target.Address).Offset(0, 1).Interior.ColorIndex = 20
Range(Target.Address).Offset(0, 2).Interior.ColorIndex = 20
Range(Target.Address).Offset(0, 3).Interior.ColorIndex = 20
Else
Target.Value = "X"
Target.Interior.ColorIndex = 3
Range(Target.Address).Offset(0, 1).Interior.ColorIndex = 22
Range(Target.Address).Offset(0, 2).Interior.ColorIndex = 22
Range(Target.Address).Offset(0, 3).Interior.ColorIndex = 22
End If

Range(Target.Address).Offset(0, 1).Select

End If
End Sub

What this code ostensibly does is turn the cells Range("B6:B49") into select switches (I want to avoid controls). When you click on a cell in range, that cell is either turned on or off (X or blank) then the corresponding Employee ID to the right of the switch is selected (this limits the activity in the switch cell to a click). This all works great UNTIL I do a sort using the buttons at the top of either C,E, or E or if I call the sort routines in any other manner. The sorts perform as expected but my "switches" no longer work. The Switches (relative B cells) are included in the sort.

Here are My sort routines:

Sub SortByEmployeeNumber()
Sheet1.Unprotect Password:="go4it"
Range("B6:E355").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
Sheet1.Protect Password:="****"
End Sub

Sub sortByEmployeeName()
Sheet1.Unprotect Password:="****"
Range("B6:E355").Select
Selection.Sort Key1:=Range("D6"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
Sheet1.Protect Password:="****"
End Sub

Sub sortByPayRate()
Sheet1.Unprotect Password:="****"
Range("B6:E355").Select
Selection.Sort Key1:=Range("E6"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
Sheet1.Protect Password:="****"
End Sub

PLEASE HELP ME! I am gong nutzzzzzzzzzz!!!

Larry

 

[Fat Cat]

A couple of possible ideas,

When you select the Range, this triggers your Selection_Change event and gives an error if more than 1 cell is selected in the range. Try changing the sort routine to remove the .select part

Similarly, at the end of the sort routine, don't select "B6" as this also triggers off the Selection_Change event and changes the value that is now located in "B6"

eg;

Sub SortByEmployeeNumber()
    Sheet1.Unprotect Password:="go4it"
    Range("B6:E355").Sort Key1:=Range("C6"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
    'Range("B6").Select        'remove this line as it triggers the selection change event again and removes any existing X
    
    Sheet1.Protect Password:="****"
End Sub

Your fancy change to Red or change to Grey/light blue toggele still worked for me after doing the sort