Problem with Worksheet Activate and Sorting

K

Kieranz

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
 
D

Dave Peterson

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.)
 
K

kieran.nayak

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.)
Click to expand...

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
 
D

Dave Peterson

What's your current code look like?

What line causes the trouble?

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

kieran.nayak

What's your current code look like?

What line causes the trouble?

What's the name of the worksheet that contains the event procedure?
Click to expand...

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
 
D

Dave Peterson

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

kieran.nayak

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.
Click to expand...

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
 
D

Dave Peterson

Glad you got it working...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Variables to SORT 4
Undo Macro Action 3
VBA 2 Codes 2
Clear Check Box 2
Modify Sort Routine to inlcude All Data 2
Sort code doesn't work on first pass, works on second 1
Sorting Question 5
Pictures not being sorted in VBA 2

Top