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