DoEvents - Code is stopping after - DoEvents

[jagrenet]

Hello Gurus,

In my quest to build a "Flashing Label" on UserForm2, my code stops processing even when using DoEvents. Everything works as expected up until the point where I "Call FLASH()". The label is actually flashing quite nicely, exactly as expected.
I have tried moving "Call FLASH()" around in the code and even adding multiple "DoEvents" in various locations but, to no avail - the code still hangs. I'm not sure what I am missing here. Any suggestions ??

Sub FLASH()

Dim y As String

y = Worksheets("Sheet3").Range("D1").Value

Do While y = ""

With UserForm2
.lblRunning.Visible = True
.Repaint
Application.Wait (Now + TimeValue("0:00:01"))
.lblRunning.Visible = False
.Repaint
Application.Wait (Now + TimeValue("0:00:01"))

End With

DoEvents

Loop



End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub cmdStart_Click()

Worksheets("Sheet3").Range("D1").ClearContents
Worksheets("Sheet3").Range("F4").ClearContents

lblClickToStart.Visible = False

On Error Resume Next

Dim Nf_Rows As Long
Dim Nf_Columns As Long
Dim wd As Word.Application
Dim doc As Word.Document
Dim wr As Word.Range 'Word.Range
Dim Path As String
Dim OpenFile As String
Dim PSAP_Name As String
Dim AppAvail As String
Dim AppAvailAVG
Dim pg As Word.Paragraph
Dim wLine As String
Dim tblcnt As Long
Dim wsRG As Worksheet
Dim y As String

Application.Wait (Now + TimeValue("0:00:01"))

With UserForm2
.lblAppAvail.Visible = True
.lblAppAvail.BackColor = &H8080FF 'Running
.lblAppAvail.Caption = "Application Availability"
.lblRunning.Visible = True
.lblRunning.BackColor = &H8080FF 'Running
End With

Application.DisplayAlerts = False
Set wsRG = ThisWorkbook.Worksheets.Add


PSAP_Name = Worksheets("Sheet3").Range("C5")
Path = "C:\Monthly_Reports\" & PSAP_Name & "\"

AppAvail = Dir(Path & "Application Availability*.pdf")


If AppAvail <> "" Then
OpenFile = Path & AppAvail

Call FLASH
DoEvents

Set wd = Word.Application
wd.Visible = False
wd.DisplayAlerts = wdAlertsNone

Set doc = wd.Documents.Open(OpenFile, False)
Set wr = doc.Paragraphs(1).Range
wr.WholeStory

wr.Copy
Debug.Print wr
wsRG.Range("I10").Formula = "=AVERAGE(D:D)"
wsRG.Paste

End If

y = wsRG.Range("I10").Value

Application.Wait (Now + TimeValue("0:00:02"))

Worksheets("Sheet3").Range("D1") = y & " % "
AppAvailAVG = y & " % "
Debug.Print y
Debug.Print AppAvailAVG
doc.Close False
wsRG.Delete 'delete the worksheet
Application.DisplayAlerts = True

Application.Wait (Now + TimeValue("0:00:02"))

UserForm2.lblRunning.Visible = False
UserForm2.lblAppAvail.BackColor = &H80FF80 'DONE
UserForm2.lblAppAvail.Caption = "Application Availability" & " " & AppAvailAVG

wd.Quit
Set wd = Nothing
Set doc = Nothing

Application.Wait (Now + TimeValue("0:00:00:15"))

UserForm2.lblRunning.Visible = True
UserForm2.lblRunning.BackColor = &H80FF80 'DONE
UserForm2.lblRunning.Caption = "Finished"

Application.Wait (Now + TimeValue("0:00:00:15"))

MsgBox "Work Complete"

cmdStart.Default = False
cmdExit.Default = True
lblClickToStart.Caption = "Click Exit To Close"
lblClickToStart.Visible = True

End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks in advance,
Jeff

 

[Skyybot]

Ok, ....... in the Call FLASH Sub, "y" is now getting the variable value which, is great. The problem now is, the Call FLASH procedure is so far down in the code that there isn't any time left for it to really "flash" the Label on the UserForm. The ultimate goal is to have it flash during the entire time the code is running so the end user has a graphical representation that "something" is actually happening. Rather than having the UserForm just sitting there Idle. Can you think of a way to refine this using the code and process I have already written ??

If you were happy with how it performed before, I would start by putting the FLASH call back where you had it originally. Then try moving the DoEvents (in FLASH) before the Do While loop. Let me know what happens.

 

[Skyybot]

Have you tried "commenting out" the DoEvents to see how it runs?

 

[jagrenet]

Have you tried "commenting out" the DoEvents to see how it runs?

Where ?? ........ in the FLASH Sub or in the cmdStart Sub ?

 

[Skyybot]

Where ?? ........ in the FLASH Sub or in the cmdStart Sub ?

In both to start. See how it operates. Then one at a time.

 

[jagrenet]

In both to start. See how it operates. Then one at a time.

I will try it .... please stand by -

 

[jagrenet]

In both to start. See how it operates. Then one at a time.

By disabling 'DoEvents in FLASH() - the application completely locks up. With it enabled, I can still close the UserForm2.

Disabling 'Do Events in cmdStart() does not seem to affect it one way or the other.

 

[Skyybot]

Can you Step In to the code and see which line it stops on?

 

[jagrenet]

Can you Step In to the code and see which line it stops on?

It gets stuck in the Do While Loop. I presume it's because "y" never gets processed and remains NULL, ...... because it is stuck in the Loop. I noticed that before and that is when I started moving "Call FLASH" around to different places in the code. And .... that's where I got stuck.

 

[Skyybot]

Try calling the flash after y has been assigned.