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

lhasha

New Member
Joined
Dec 13, 2005
Messages
1
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
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;

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,253
Members
412,709
Latest member
Rishu
Top