Sorting with worksheet activate

K

Kieranz

Hi,
Greetings. I have shtPlyrs and shtDraw. The shtPlyrs has ColA Lastname
ColB Firstname and ColC Town. ColD is concatenate of Firstname &
Lastname & Town. ColD is then name ranged as LIST. The LIST is then
used in the shtDraw via data validation with dropdown. The players
are added as the entries are received to the shtPlyrs. However in the
shtDraw the LIST has to be in alpha order by firstname and then
lastname.

Here is my code which I tried with help of macro recorder:
General Module:

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

Problem: When I select the shtDraw, I get a partial overlap of the
shtPlyrs details and on debug I notice that the sort selection in
shtPlyrs continues to remain selected. How do I get to deselect the
sort selection so that I can use the dropdown in the cell to choose my
player from an alpha sorted LIST.
Using Vista HP with Excel 2007. Many thks.
Rgds KZ
 

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

Top