PDA

View Full Version : End Sub statements givin me all kinds of Hourglass



tdm100
09-12-2006, 01:24 AM
I wrote code that utilizes 10 different UserForms.
At the Module Level I have two Sub Procedures.
One is the obligatory Show First UserForm and is initiated from a button on a worksheet.
The second Sub Procedure is not accessed unless it is called by one of the other nine UserForm Code (dependent on users action or input).

Generally, most UserForm Code ends with "prep" setting up the next UserForm.

for example from UserForm1 code

With frmUserForm2
.txtTextbox1.Text = info1
.txtTextbox2.Text = info2
.Show
End With
End Sub

It is possible during a user session to cycle through all UserForms 10 to 50 times.

I have discovered that when the session has concluded, I get the dreaded Hourglass Icon because the execution is going through all the code and executing all the accumulated "End Sub" Statements.

Including the very first Sub Procedure

Sub OpenFirstUserForm()
frmUserForm1.Show
End Sub


Which is usually the LAST End Sub statement Excel eventually executes.

After potentially fifty cycles, there are a lot of End Sub statements not being executed!
Only at the end of the session does all those End Subs get executed.
Thus, producing the Hourglass Icon.

Just my way of doing things, but anytime I write code that produces the Hourglass, that's kinda of an indicator to me that I aint doin something right or that maybe I am missing something!

But for the life of me I can't figure out how to end a procedure, particularly the very first one, until the user session has completed.

As far as I can tell, it's the nature of the beast, the way things are.
Correct?

~VBA tags ROCK!~

Dave
09-13-2006, 07:26 PM
How about "Unload Me" code before the session ends.. or instead of hiding a userform before presenting another one, use "Unload Me" to exit each userform. This may be of no help but may be worth a trial. Dave

tdm100
09-13-2006, 09:40 PM
How about "Unload Me" code before the session ends.. or instead of hiding a userform before presenting another one, use "Unload Me" to exit each userform. This may be of no help but may be worth a trial. Dave

I appreciate your reply Dave.
All user forms have a command click sub that unload that particular userform.
within the code, the statement just prior to the "prep" "with end with" statements is the ---unload
Naturally, to unload the previous userform.

If you take a look at the very first sub in the sequence that's the only "Show" command that's not proceeded with an unload.

Puttin a unload statement behind the first Show command aint goin to help me much.
In fact it will result in a Compile error



Sub OpenFirstUserForm()
frmUserForm1.Show
End Sub
'instead write it as...


Sub OpenFirstUserForm()
frmUserForm1.Show
Unload Me
End Sub


Even if it did work, by chance it did recognize that "Unload Me",
whatever input the user did on the now visible userform1, the execution will now take it to the Userform1 Code page.
That Unload Me wont get executed until the end of the session.
Which by then wont have nothing to unload.

Here's the situation:

'Module1....................
Sub OpenFirstUserForm()
frmUserForm1.Show
'End Sub does not get executed until the end of session
End Sub
Sub ColorITnPostit(x As Integer, ParamArray pa() As Variant)
Windows("book1.xls").Activate
Sheets("sheet1").Select
'do dirty deeds to the info, color it and then post it on sheet one
'This End Sub always gets executed Immediately
End Sub
'frmUserForm1.......................
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub CommandButton1_Click()
FirstAction 1
'End Sub does not get executed Immediately
End Sub
Sub FirstAction(HitME)
TotActs = HitME
SomAry=array("Head","Chin","Cheek", "Nose)

Unload Me

ColorITnPostit HitME, SomAry
With UserForm2
.txtBox1.Text = TotActs
.Show
End With
'End Sub does not get executed Immediately
End Sub

'frmUserForm2................
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub CommandButton1_Click()
SeondAction 2
'End Sub does not get executed Immediately
End Sub
Sub SecondAction(HitME)
SomAry=array("Ear","Neck","Fist", "Eye")
FstActs = .txtBox1.Value
SecAct = HitME + 1
TotActs = FstActs + SecAct

Unload Me
ColorITnPostit SecAct, SomAry
With UserForm3
.txtBox1.Text = TotActs
.txtBox2.Text = FstActs
.txtBox3.Text = SecAct
.Show
End With
'End Sub does not get executed Immediately
End Sub

'Etc., Etc. for the othe UserForms up to ten



When the session ends,,, it will go back and HIT the End Subs it didnt execute

tdm100
09-13-2006, 10:11 PM
This isnt the actual code btw,,,,just an on the fly representation of it

johnske
09-14-2006, 12:35 AM
If you show all forms non-modally you can have an activate event for the 2nd form to unload the 1st e.g.
Option Explicit

Private Sub UserForm_Click()
'this is in UserForm1
UserForm2.Show False
MsgBox "UserForm1 procedure ended"
End Sub


Option Explicit

Private Sub UserForm_Activate()
'this is in UserForm2
Unload UserForm1
End Sub
which Unloads UserForm1 but doesn't Hide it... :)

johnske
09-14-2006, 12:54 AM
Also, you won't get a compile error if you put the Unload before the Show. Once the Unload command is given, all code following the Unload statement must be executed before it can be unloaded... e.g.
Option Explicit

Private Sub UserForm_Click()
'this is in UserForm1
Unload Me
UserForm2.Show False
MsgBox "UserForm1 procedure ended"
End Sub
which both Unloads and hides UserForm1