Flashing Code Help

[McM]

I have found this code and modified it to my needs, however it is not working when the File is opened.

Cell J10 already has Clear Reports Folder, so when I open the file, I want J10 to flash without having to type anything.


Please, can anyone help me with this??

Thanks in advance for your help.



Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J10").Value = "Clear Reports Folder" Then
For n = 1 To 10
Range("J10").Font.Color = vbRed
Delay (0.25)
Range("J10").Font.Color = vbWhite
Delay (0.25)
Next n
ElseIf Range("J10").Value <> "Clear Reports Folder" Then
For n = 1 To 10
Range("C1").Font.Color = vbRed
Delay (0.25)
Range("C1").Font.Color = vbWhite
Delay (0.25)
Next n
End If
Range("J10,C1").Font.Color = vbBlack
End Sub


Sub Delay(rTime As Single)
'delay rTime seconds (min=.01, max=300)
Dim oldTime As Variant
'safety net
If rTime < 0.01 Or rTime > 300 Then rTime = 1
oldTime = Timer
Do
DoEvents
Loop Until Timer - oldTime > rTime
End Sub

 

[j844929]

Could you just put the script under a different command, ie:

Sub Workbook_Open() ...?

That would action the script when you open the file.

Or have I missed the point?

 

[McM]

For example, I copy the code in Module1 and then in the worksheet code do I put

Call Module1.Workbook_Open



???

 

[Smitty]

Welcome to the Board!

Put this in the "ThisWorkbook" module:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">If</SPAN> Range("J10").Value = "Clear Reports Folder" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> n = 1 <SPAN style="color:#00007F">To</SPAN> 10
            Range("J10").Font.Color = vbRed
            Delay (0.25)
            Range("J10").Font.Color = vbWhite
            Delay (0.25)
        <SPAN style="color:#00007F">Next</SPAN> n
            <SPAN style="color:#00007F">ElseIf</SPAN> Range("J10").Value <> "Clear Reports Folder" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> n = 1 <SPAN style="color:#00007F">To</SPAN> 10
            Range("C1").Font.Color = vbRed
            Delay (0.25)
            Range("C1").Font.Color = vbWhite
            Delay (0.25)
        <SPAN style="color:#00007F">Next</SPAN> n
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Range("J10,C1").Font.Color = vbBlack
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

And this in a General module:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Delay(rTime <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>)
    <SPAN style="color:#007F00">'delay rTime seconds (min=.01, max=300)</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> oldTime <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#007F00">'safety net</SPAN>
        <SPAN style="color:#00007F">If</SPAN> rTime < 0.01 <SPAN style="color:#00007F">Or</SPAN> rTime > 300 <SPAN style="color:#00007F">Then</SPAN> rTime = 1
            oldTime = Timer
        <SPAN style="color:#00007F">Do</SPAN>
            DoEvents
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Timer - oldTime > rTime
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then it will execute when the workbook is open. Note that you may want to Application.Goto Reference:=[Sheet1'!J10] at the beginning of teh code to make sure that you're on the right sheet.

Hope that helps,

Smitty

 

[McM]

Thank you Smitty, works like a charm. I was putting the code in the worksheet tab and not in "This Workbook".