Hi
try changing the line
Worksheets(activeSheetName).Select
to
application.enableevents=false
Worksheets(activeSheetName).Select
application.enablevents=true
--
Regards
Frank Kabel
Frankfurt, Germany
"BountyHunter " schrieb
im Newsbeitrag ...
Hi,
Am new to VBA, although am an experienced LotusScript developer and
the
syntax is more or less identical.
One limitation i am finding in VBA (BTW am using Excel 2000) is that
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 sheet
(ie Deactivate event).
However I code it I keep ending up in an infinite loop, because to
sort
I have to go back to the sheet, and when i try to switch back to the
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
a
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 Sub
---
Message posted from http://www.ExcelForum.com/