ExcelBanter - Problem with Worksheet Activate and Sorting

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Problem with Worksheet Activate and Sorting (https://www.excelbanter.com/excel-programming/424976-problem-worksheet-activate-sorting.html)

Problem with Worksheet Activate and Sorting

Hello All,
In the General Module I have:
Sub proSortByName()
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("B10:B80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("A10:A80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Plyrs").Sort
.SetRange Range("A10:C80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

Problem with Worksheet Activate and Sorting

I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
with ActiveWorkbook.Worksheets("Plyrs")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B10:B80"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.Sort.SortFields.Add Key:=.Range("A10:A80"), _
sortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.sort.SetRange .Range("A10:C80")

with .sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
me.Range("A3").Select
End Sub

(Untested, uncompiled. Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("B10:B80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("A10:A80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Plyrs").Sort
.SetRange Range("A10:C80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Problem with Worksheet Activate and Sorting

On Mar 3, 7:12*pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
* *with ActiveWorkbook.Worksheets("Plyrs")
* * * *.Sort.SortFields.Clear
* * * *.Sort.SortFields.Add Key:=.Range("B10:B80"), _
* * * * * *SortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.Sort.SortFields.Add Key:=.Range("A10:A80"), _
* * * * * *sortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.sort.SetRange .Range("A10:C80")

* * * *with .sort
* * * * *.Header = xlGuess
* * * * *.MatchCase = False
* * * * *.Orientation = xlTopToBottom
* * * * *.SortMethod = xlPinYin
* * * * *.Apply
* * * *End With
* *end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
* Call proSortByName
* me.Range("A3").Select
End Sub

(Untested, uncompiled. *Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Clear
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("B10:B80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("A10:A80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *With ActiveWorkbook.Worksheets("Plyrs").Sort
* * * *.SetRange Range("A10:C80")
* * * *.Header = xlGuess
* * * *.MatchCase = False
* * * *.Orientation = xlTopToBottom
* * * *.SortMethod = xlPinYin
* * * *.Apply
* *End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

Problem with Worksheet Activate and Sorting

On Mar 4, 6:20*pm, Dave Peterson wrote:
What's your current code look like?

What line causes the trouble?

What's the name of the worksheet that contains the event procedure?

wrote:

On Mar 3, 7:12 pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
* *with ActiveWorkbook.Worksheets("Plyrs")
* * * *.Sort.SortFields.Clear
* * * *.Sort.SortFields.Add Key:=.Range("B10:B80"), _
* * * * * *SortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.Sort.SortFields.Add Key:=.Range("A10:A80"), _
* * * * * *sortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.sort.SetRange .Range("A10:C80")

* * * *with .sort
* * * * *.Header = xlGuess
* * * * *.MatchCase = False
* * * * *.Orientation = xlTopToBottom
* * * * *.SortMethod = xlPinYin
* * * * *.Apply
* * * *End With
* *end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
* Call proSortByName
* me.Range("A3").Select
End Sub

(Untested, uncompiled. *Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Clear
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("B10:B80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("A10:A80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *With ActiveWorkbook.Worksheets("Plyrs").Sort
* * * *.SetRange Range("A10:C80")
* * * *.Header = xlGuess
* * * *.MatchCase = False
* * * *.Orientation = xlTopToBottom
* * * *.SortMethod = xlPinYin
* * * *.Apply
* *End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

--

Dave Peterson

Dave, thks for your response. Scenerio is: Sheet 1 is "Plyrs" Sheet 2
is "Draw". "Plyrs" has Col A Lastname Col B Firstname and Col C Town.
I then have Col D Fullname which is concatenation of Firstname &
Lastname & Town. As players (about 180 - 200) report we record their
details in the Plyrs sheet. Latter we are required to come up with a
Draw Sch according to fixed times and group the players in pairs.
(similar to golf if u r a golfer :) ). So I have defined Col D in
"Plyrs" as LIST name range. In the Draw sheet I use the LIST via the
validation dropdown arrow to pick the player. To make the player
selection easy in the Draw sheet the LIST is to be in alpha order. So
to the Draw sheet i have attached the Worksheet_activate sub which
then calls the SortByName procedure in the general module.

The idea being that everytime i click the Draw sheet the LIST will be
in alpha order. I hope u follow the drift Dave, sorry about my english
grammer.

Issue: the codes are executing okay no errors. But when I am in the
Draw sheet a portion of the "SetRange Range("A10:C80") is showing thru
(I suspect because of my other fancy formattings); ie its like an
overlay. And when I go to the Plyrs sheet the SetRange is highlighted
as selected. How do I get the code to deselect the SetRange? Or is
there a better way to recode the whole thing?
Much obliged and gratified for your and other experts like u who take
time and effort to help us, particularly for me coming from Zambia
Africa where access to both people and internet is very hard. Thks
again. Rgds KZ

Problem with Worksheet Activate and Sorting

On Mar 5, 3:39*pm, Dave Peterson wrote:
It's very rare that you have to select anything to work with it. *And if you
don't select stuff, the code becomes easier to read/maintain and debug.

I wouldn't rely on selecting a range for the stuff you're doing.

But I really don't have a guess why the sort code is failing. *Maybe you can
post your current code and indicate any line that is causing errors.

wrote:

On Mar 4, 6:20 pm, Dave Peterson wrote:
What's your current code look like?

What line causes the trouble?

What's the name of the worksheet that contains the event procedure?

wrote:

On Mar 3, 7:12 pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
* *with ActiveWorkbook.Worksheets("Plyrs")
* * * *.Sort.SortFields.Clear
* * * *.Sort.SortFields.Add Key:=.Range("B10:B80"), _
* * * * * *SortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.Sort.SortFields.Add Key:=.Range("A10:A80"), _
* * * * * *sortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.sort.SetRange .Range("A10:C80")

* * * *with .sort
* * * * *.Header = xlGuess
* * * * *.MatchCase = False
* * * * *.Orientation = xlTopToBottom
* * * * *.SortMethod = xlPinYin
* * * * *.Apply
* * * *End With
* *end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
* Call proSortByName
* me.Range("A3").Select
End Sub

(Untested, uncompiled. *Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Clear
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("B10:B80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("A10:A80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *With ActiveWorkbook.Worksheets("Plyrs").Sort
* * * *.SetRange Range("A10:C80")
* * * *.Header = xlGuess
* * * *.MatchCase = False
* * * *.Orientation = xlTopToBottom
* * * *.SortMethod = xlPinYin
* * * *.Apply
* *End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

--

Dave Peterson

Dave, thks for your response. Scenerio is: Sheet 1 is "Plyrs" Sheet 2
is "Draw". "Plyrs" has Col A Lastname Col B Firstname and Col C Town.
I then have Col D Fullname which is concatenation of Firstname &
Lastname & Town. As players (about 180 - 200) report we record their
details in the Plyrs sheet. Latter we are required to come up with a
Draw Sch according to fixed times and group the players in pairs.
(similar to golf if u r a golfer :) ). So I have defined Col D in
"Plyrs" as LIST name range. In the Draw sheet I use the LIST via the
validation dropdown arrow to pick the player. *To make the player
selection easy in the Draw sheet the LIST is to be in alpha order. *So
to the Draw sheet i have attached the Worksheet_activate sub which
then calls the SortByName procedure in the general module.

The idea being that everytime i click the Draw sheet the LIST will be
in alpha order. I hope u follow the drift Dave, sorry about my english
grammer.

Issue: the codes are executing okay no errors. But when I am in the
Draw sheet a portion of the "SetRange Range("A10:C80") is showing thru
(I suspect because of my other fancy formattings); ie its like an
overlay. And when I go to the Plyrs sheet the SetRange is highlighted
as selected. How do I get the code to deselect the SetRange? Or is
there a better way to recode the whole thing?
Much obliged and gratified for your and other experts like u who take
time and effort to help us, particularly for me coming from Zambia
Africa where access to both people and internet is very hard. Thks
again. Rgds KZ

--

Dave Peterson

Hi Dave, the code used is substantially yours as follows:
Option Explicit
Sub proSortByName()
with ActiveWorkbook.Worksheets("Plyrs").sort
.SortFields.Clear
.SortFields.Add Key:=.Range("B10:B80"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=.Range("A10:A80"), _
sortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.SetRange .Range("A10:C80") 'PLS NOTE THIS IT REMAINS SELECTED
when you go back to "Plyrs" sheet

.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'I ADD THE BELOW TO GET RID OF THE .SetRange
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SortFields.Clear
.SetRange Range("B3:B3")
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Application.goto Reference:=range("A100"), scroll=true 'IT REMOVED
THE OVERLAP I WAS GETTING on the Draw sheet
me.Range("A3").Select
End Sub

Dave, I assume my coding is not appropriate but its working dont know
why!
Many many thks i have learnt quite bit, pls keep up the good work
you'r doing
Take care and again thks
KZ

Problem with Worksheet Activate and Sorting