une


I tried to run a simple loop I created as a macro employing a user form that contains a label and a command button. I want the label caption to alternate between OFF and ON as the command button is clicked. There must have been an error of some sort as when I ran the macro it froze MS Word completely. No compile error ever appeared and the macro DID begin to run without protest. I looked at the task manager and everything under MS Word was "not responding". I ended the macro under the task manager and MS Word completely shut down!

Here is the code. I realize it is not structured correctly, but should trying to run it cause MS Word to to freeze up and require a shut down via the task manager Surely it can't have created that big a problem.

This is the "offon" macro code;

Sub offon()
'
' offon Macro
' Macro created 23/02/2007 by une
'

UserForm1.Show
End Sub

This is the UserForm1 code;

Option Explicit

Private Sub CommandButton1_Click()
Dim count As Integer
count = 1
Dim status As Boolean

Do
If (count Mod 2 <> 0) Then
status = False
Else
status = True
End If

If (status = False) Then
Label1.Caption = "OFF"
Else
Label1.Caption = "ON"
End If
Loop
End Sub

Private Sub Label1_Click()

End Sub
  


Is ALL the code between

Private Sub CommandButton1_Click()

and
End Sub

executed every time the command button is clicked When is the "offon" macro code executed If the "Sub CommandButton1_Click()" code is executed every time the command button is clicked I am starting to see my problem (although the Word shutdown still confuses me). Furthermore if this is the case, how and where do I place code that executes only once at the start of the entire macro execution I am starting to think that the declaration and initialization of the variable "count" should be done only once before the command button is clicked, then once clicked the sub containing the loop takes over. The interplay between the "offon" macro code and the "UserForm1" code is confusing me. Perhaps putting the some declarations in the "offfon" macro code rather than in the "UserForm1" code would make them Global and solve my problem I am rambling now, any help appreciated.

As you can see I am still trying to get my mind around how the event driven nature of VBA operates and how macros and modules interact.

 




Re: Loop causing MS Word freeze

Andy Pope


Hi,

Word is freezing because there is no code to terminate the Do Loop in your button click event.
You do not need to loop within that routine. VBA will call the routine when the user clicks the button.

Try this modification to your code. You can see I have removed the loop. I also changed the Count variable to PressCount and moved it's declaration to the top of the code module. This will make the variable available to all routines in the userform code and allow it to retain it's value between button presses. With the variable set to 1 in the click event it would always set the label to ON


Option Explicit

Dim PressCount As Integer

Private Sub CommandButton1_Click()

Dim status As Boolean

PressCount = PressCount + 1
If (PressCount Mod 2 <> 0) Then
status = False
Else
status = True
End If

If (status = False) Then
Label1.Caption = "OFF"
Else
Label1.Caption = "ON"
End If

End Sub







Re: Loop causing MS Word freeze

une

Thnaks a lot, I will give it a try. Your explanation makes clear sense to me.





Re: Loop causing MS Word freeze

une

OK I am getting somewhere now. However please consider the following code. Is "count" a global variable available for use by all following Subs What is its initial value without an assignment statement How would I give it an initial value I tried to enter the line;

count = 1

on the line following its declaration, but an "invalid outside procedure" compilation error occurred. Why is assigning an initial value to count outside all Subs a problem

Option Explicit
Dim count As Integer


Private Sub CommandButton1_Click()
If (count Mod 2 <> 0) Then
Label1.Caption = "OFF"
Label2.Caption = count
Else
Label1.Caption = "ON"
Label2.Caption = count
End If
count = count + 1
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label2_Click()

End Sub

I am used to C++ and I am finding VBA quite different in the way it operates, but I will get there. Would the following anaolgy be correct (I don't think it is)

"The code prior to the Subs in VBA is like the main() function in C++ and the code in the Subs is like the other functions used by main() in C++."





Re: Loop causing MS Word freeze

Andy Pope

As Count is declared as a Integer the initialize value wil be zero.

If you want to specify a value for the variable you need to do so in your code before the variable is used. In this particular case as you are using a userform the Initialize event of the userform is where you would set the value.

Private Sub UserForm_Initialize()

Count = 1

End Sub






Re: Loop causing MS Word freeze

une

Thanks again. I have got it working to my satisfaction now. I have noticed though that when running the macro that if the user does not pause for approximately one second between clicks, the Command _button() Sub does not execute, ie click too fast and the switch from "off" to "on" or visa versa does not happen. Why is this Is there a timing issue with mouse clicks



Re: Loop causing MS Word freeze

Andy Pope

I would guess it probably is. Add this code and it will report doubleclicks

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Label1.Caption = "DOUBLECLICK"

End Sub