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]

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.

 

[jagrenet]

Try calling the flash after y has been assigned.

Ok, ...... moved Call FLASH to the line below "y = wsRG.Range("I10").Value" ....... y actually gets thhe value it is supposed to, the Call FLASH happens and it goes to the Loop ....... when I checked the y Variable in the Loop ...... it remains NULL. Is this possibly because cmdStart() is a "Private" Sub ??? - I am checking that now.