Sort sheet on sheet's Deactivate event

[BountyHunter]

Hi,

Am new to VBA, although am an experienced LotusScript developer and th
syntax is more or less identical.

One limitation i am finding in VBA (BTW am using Excel 2000) is tha
there are less events to work with.

I am struggling with one problem in particular.

I want a particular sheet to be sorted when a user leaves that shee
(ie Deactivate event).

However I code it I keep ending up in an infinite loop, because to sor
I have to go back to the sheet, and when i try to switch back to th
sheet the user has moved to, the Deactivate event kicks in again...
Help!

The below is the code is from the Deactivate event of the "Courses
sheet. It is trying to sort the sheet and then populate the list of
combobox on another sheet.

Any help would be appreciated.

Thanks
Martin


Private Sub Worksheet_Deactivate()
Dim shtTournament
Dim shtCourses
Dim shtDifficulty
Dim shtActive
Dim comboCourse
Dim i
Dim rows

Set shtTournament = Worksheets("Tournament")
Set shtCourses = Worksheets("Courses")
Set shtDifficulty = Worksheets("Difficulty")
activeSheetName = ActiveSheet.Name
Set comboCourse = shtTournament.comboCourse

' clear the current entries from combobox
shtTournament.comboCourse.Clear

' sort the Courses worksheet
shtCourses.Select

rows = shtCourses.UsedRange.rows.Count

shtCourses.Range(shtCourses.Cells(3, 1), shtCourses.Cells(rows
38)).Select
Selection.Sort Key1:=shtCourses.Range("A3"), Order1:=xlAscending
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' loop through each course and add to comboCourse
If rows = 2 Then
comboCourse.AddItem ("Add Courses to the Courses sheet")
Else
comboCourse.AddItem (" ")
For i = 3 To rows
If Not shtCourses.Cells(i, 1) = "" Then
comboCourse.AddItem (shtCourses.Cells(i, 1))
End If
Next
End If

Worksheets(activeSheetName).Select

End Su

 

[Frank Kabel]

Hi
try changing the line
Worksheets(activeSheetName).Select

to
application.enableevents=false
Worksheets(activeSheetName).Select
application.enablevents=true

 

[Anders S]

Martin,

Probably there is no need to "go back" as the _Deactivate code operates on the
sheet you are leaving, although the destination sheet is the ActiveSheet when
the code runs.

This works for me:

'*****
Option Explicit

Private Sub Worksheet_Deactivate()
MsgBox Me.Name
Range("A1:N8").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
'*****

HTH
Anders Silven

 

[BountyHunter]

Thanks guys. You solved the problem