Issues with DoEvents command

[Andrew]

For the code below, if I leave the worksheet alone, the code runs
fine. If I activate a cell by selecting it, and leave it active, the
code doesn't complete. How do I write a code which will complete the
code regardless of what is happening on the sheet?

thanks

Private Sub CommandButton1_Click()
For k = 1 To 10
Cells(k, 1) = k
Start = Timer
Do While Abs(Timer - Start) <= 1 'Waiting 1 sec
DoEvents
Loop
Cells(k, 2) = k + 1
Next
End Sub

 

[Jim Thomlinson]

What exactly are you trying to accomplish? I understand that you want code
execution to continue while the user is working in the sheet but that is a
VERY hit and miss proposition depending what you are up to...

Do events allows the current stream of exectution to be suspended while
other code is run but that is not what you are doing. To see doevents in
action try this...

Add 2 command buttons (from the control toolbox) to a worksheet. Place this
code within the sheet itself

Private Sub CommandButton1_Click()
MsgBox "Tada"
End Sub

Private Sub CommandButton2_Click()
Dim lng As Long

For lng = 1 To 100000
Application.StatusBar = lng
'DoEvents
Next lng
Application.StatusBar = False
End Sub

Click button 2 to execute the counter in your status bar. While it is
executing click Button 1. Nothing happens until the code from button 1
completes.

Now uncomment DoEvents and try again.

This time you can suspend the execution of the counter at any point to run
the message box.

 

[EricG]

I interpret what you wrote as "I press CommandButton1 to start the macro, and
then I double-click in a cell to activate it". If this is what you're
talking about, try my addition to your code below, which intercepts the
double-click and prevents it if the routine is running.

HTH,

Eric

Option Explicit

Public I_Am_Running As Boolean

Private Sub CommandButton1_Click()
Dim k As Long
Dim start As Double
'
I_Am_Running = True
For k = 1 To 10
Cells(k, 1) = k
start = Timer
Do While Abs(Timer - start) <= 1 'Waiting 1 sec
DoEvents
Loop
Cells(k, 2) = k + 1
Next
I_Am_Running = False
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If I_Am_Running Then Cancel = True
End Sub

 

[Andy]

What exactly are you trying to accomplish? I understand that you want code
execution to continue while the user is working in the sheet but that is a
VERY hit and miss proposition depending what you are up to...

Do events allows the current stream of exectution to be suspended while
other code is run but that is not what you are doing. To see doevents in
action try this...

Add 2 command buttons (from the control toolbox) to a worksheet. Place this
code within the sheet itself

Private Sub CommandButton1_Click()
MsgBox "Tada"
End Sub

Private Sub CommandButton2_Click()
Dim lng As Long

For lng = 1 To 100000
Application.StatusBar = lng
'DoEvents
Next lng
Application.StatusBar = False
End Sub

Click button 2 to execute the counter in your status bar. While it is
executing click Button 1. Nothing happens until the code from button 1
completes.

Now uncomment DoEvents and try again.

This time you can suspend the execution of the counter at any point to run
the message box.

What I'm trying to do is difficult to explain, but in a nutshell I
want the code to hold a value of a boolean variable (not a cell) for 1
second and then put the value of that variable to zero. But I don't
want the processor waiting for the 1 second, I want it available to do
other things. So in your code example, if I interrupt it by hitting
command button 1, the counter code continues after a slight delay. In
my code, the counter stops. Why?