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.
Loop causing MS Word freeze
bhoopathi
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
Peter Rook
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
Gurpreet Singh Gill
Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Label1.Caption = "DOUBLECLICK"
End Sub
Michael J Brown
Learning VB
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++."
Ondrus