Sort sheet on sheet's Deactivate event
 

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

--
Message posted from http://www.ExcelForum.com

Sort sheet on sheet's Deactivate event
 

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/

Sort sheet on sheet's Deactivate event
 

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 " skrev i
meddelandet ...
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/

Sort sheet on sheet's Deactivate event
 

Thanks guys. You solved the problem

--
Message posted from http://www.ExcelForum.com