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

 

[jagrenet]

Try calling the flash after y has been assigned.

I removed "Private" and stepped through - same results. "y" is still NULL in the Loop Sub..

 

[Skyybot]

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.

That sounds reasonable. Let me know what happens. If that's the case, you can add an argument to the FLASH Sub and carry the variable.

 

[jagrenet]

That sounds reasonable. Let me know what happens. If that's the case, you can add an argument to the FLASH Sub and carry the variable.

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 ??

 

[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.

 

[jagrenet]

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.

I will try that and report my findings. Thank you !!

 

[jagrenet]

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.

Hahahahaha ........ Now, ..... it's getting throough the code all the way but, it stopped flashing LOL - back to square 1.