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