Use one procedure to end another

[johnske]

Hi,

I have a fairly large number of worksheets with a smaller number of procedures. Each procedure activates 2 or more sheets in turn so as to animate a slightly different picture that is showing on each sheet and a Pause function displays each active sheet for 3/4 sec so the differences can be observed. A typical procedure is this...

Sub ComparePoorStance()
Dim N As Long
ExitSub = False
HelpForm.Show False
For N = 1 To 12
If ExitSub Then GoTo Finish
If ActiveSheet.Name = "BadBody" Then
Sheets("GoodBody").Activate
Application.Goto [A1], True
[J15] = "GOOD"
Else
Sheets("BadBody").Activate
Application.Goto [A1], True
[J15] = "BAD"
End If
Pause 0.75
Next
Finish:
Sheets("BadBody").[J15].ClearContents
Sheets("GoodBody").[J15].ClearContents
End Sub

The HelpForm is merely a UserForm telling the user they can cancel the animation by right-clicking the sheet, and ExitSub is a public (Boolean) variable that's made True as follows...


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Unload HelpForm
ExitSub = True
End Sub

All of this works fine except for one thing - on ending the procedure one of the sheets becomes the active sheet and if you click the sheet tab to go to another sheet this other sheet becomes active for a split second but the (first) active sheet is then activated again. (which is REALLY annoying as it means you must click a sheet tab TWICE to get to another sheet).

It should be simple to remedy this situation but I've tried many things to no avail - any suggestions gratefully received :)

TIA,
John

[brettdj]

John,

Not 100% sure I understand this but ... can u pls post an example

Cheers

Dave

[johnske]

Hi Dave, WIP attached... :)

E.G. Follow one of the 'Elbow' links - click 'Animate' - right-click on the w/s, then click 'StringHand' sheet tab.

(Try doing this a couple of times cos it doesn't always do it)

Regards,
John

[johnske]

Thanx to all that may have had a look at this, I've finally tracked down the problem though. :)

The main problem lay within the procedures where three sheets are activated sequentially within the For-Next loop, with the condition 'If ExitSub Then GoTo Finish' being checked at the start of the loop before the 1st sheet is activated.

The solution was to check for this condition prior to EVERY sheet being activated (and not just at the beginning of the iteration), as in...


Sub CheckThumb()
Dim N As Long
ExitSub = False
HelpForm.Show False
For N = 1 To 8
If ExitSub Then GoTo Finish
Sheets("LoadedFinger").Activate
Application.Goto [A1], True
Pause 0.75
If ExitSub Then GoTo Finish
Sheets("CorrectThumb").Activate
Application.Goto [A1], True
Pause 0.75
If ExitSub Then GoTo Finish
Sheets("LoadedThumb").Activate
Application.Goto [A1], True
Pause 0.75
Next
Finish:
Unload HelpForm
End Sub:thumb