Text Effects In Excel

[monirg]

Is there a way to make a text in a cell blinking, or shimmering, or sparkling!!. Only the text, not the fill and not a text box.

Thank you.

 

[Greg Truby]

Hello again, Monir. Take a look at Tushar's solution which he was good enough to reference on this thread a while back.

 

[monirg]

Greg;
Thanks again for your help.

1. So far, I'm able to have the cell border flashing, regardless of the cell contents!!

2. If the cell I want to "flash" has the function, say:
IF(A=B, "", "Wrong")
and in the cell conditional formatting, I have:
Condition 1:
Formula Is ~~~~~=MOD(SECOND(NOW()),2) ~~~~~ =1

Condition 2:
Cell Value Is ~~~~~~~~not equal to ~~~~~~~~~~ =""

then why the text Wrong does not flash with the cell border if it was the value returned in the cell?

3. What is the appropriate Worksheet event to include Tushar's RepeatOneSec() timer macro in? For the ones I tried (SelectionChange, Change), you need to activate a cell (any cell) on the w/s when you open the w/b before the fashing starts ?

 

[Greg Truby]

Monir,

In most cases one would need to combine Tushar's generic condition with another using AND(). For example, in your case, the condition for flashing might look like:

Formula Is: =AND(A1="Wrong",MOD(SECOND(NOW()),2)=1)

As for where to locate it: the code for RepeatOneSec() and EndProcess() can be in a standard module. Then in your workbook's code module, you simply call RepeatOneSec, i.e.

Private Sub Workbook_Open()
    RepeatOneSec
End Sub

Were it me, I would probably give the user a means of shutting this off. For example, create a toggle button, in my case, naming the button - toggFlashingText and then on the worksheet's code page, I have the code for the toggle button...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> toggFlashingText_Click()
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Me.toggFlashingText <SPAN style="color:#00007F">Then</SPAN>
        EndTimerProcess
        Me.toggFlashingText.Caption = "Enable Flashing Text"
    <SPAN style="color:#00007F">Else</SPAN>
        RepeatOneSec
        Me.toggFlashingText.Caption = "Disable Flashing Text"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Using this approach, one needs to modify the workbook's Open event handler a bit. If you want the text flashing enabled at startup, you need to make sure that the toggle button's status reflects this, so your open method might look like:

<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Me.Sheets(1).toggFlashingText <SPAN style="color:#00007F">Then</SPAN>
        Me.Sheets(1).toggFlashingText = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        RepeatOneSec
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Also, judging by the behavior I'm getting from Excel, if you do not turn off the timer event and try to close the workbook, the OnTime event remains linked to the procedure in the workbook and wants to reopen the workbook so it can run the processes set for OnTime. So I added the following to turn it off:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    EndTimerProcess
End <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
HTH

 

[tusharm]

Monir contacted me privately and I referred her/him to Greg's detailed response, which should adress all of Monir's questions and then some. {g}

Well thought through reasoning, Greg. One quibble. The end procedure on my web site is EndProcess not EndTimerProcess, though your name is definitely more descriptive.

 

[Greg Truby]

...The end procedure on my web site is EndProcess not EndTimerProcess, though your name is definitely more descriptive.

Tushar, yep, my oversight. I changed the name in my test WB to be a bit more descriptive and forgot to mention that change anywhere in the body of my post. Thanks for pointing that out.

Regards,

 

[monirg]

Greg;

Thanks again for your tremendous help. You clearly understand how these things work, and you do so with such ease and dexterity!
Some comments:
1. It appears that a good location of the timer is the worksheet event SelectionChange. The workbook Open event didn't accomplish the task for whatever reason. The workbook BeforeClose event is an excellent idea to turn off the timer and close the w/b without hassle!

2. Your suggested toggle button to switch the timer off and on at will is a very practical idea and it works fine. However, it would defeat the purpose of having the cell flashing in the first place! you would like the cell to continue flashing as long as the conditional format is TRUE and until the algorithm is fixed and the conditional format becomes FALSE.

3. Couple of small problems remaining:
a) the text in the cell DOES NOT flash! Only the cell Border and Pattern
flash ! Is this the definition of cell flashing ?

b) when I open the workbook and select the w/s, I still have to click a cell (any cell) on the w/s to activate the flashing effects (assuming that the conditional format is TRUE).

Regards.

 

[Greg Truby]

Monir,

1. The WB Open event only fires when you open the workbook. Although you can position the cursor inside the procedure and hit your F5 key to run it manually in order to test it.

2. The toggle button is just a thought in case you have a user going "Yeah, yeah, I know! Now stop blinking for a minute, would ya!!!"

3.a. The "Flash" is between whatever your default format is and whatever your conditional format is set to be. So you'd need to alter the "pattern" in the conditional format's format dialog box before the fill color would blink.

3.b. Yes, that's correct; you would if you elect to put the first call to RepeatOneSec() in the SelectionChange event handler. You might be happier putting it in the Worksheet_Activate() event handler instead. You might also want to move the call to EndProcess (or EndTimerProcess if you used my name instead of Tushar's) in the worksheet_deactivate() event handler. That way if you change worksheets you don't have the ongoing overhead of Timer/format updates when you don't need them.

Regards,

 

[monirg]

Greg;
It works ... Thanks for your help.

 

[monirg]

Greg;

Having Now the cell blinks nicely and quietly, the entire w/s appears to be "vibrating" at the same 1-sec intervals ! including the mouse pointer, the task bar "Ready", the tool bar Icons, the macro Buttons, etc.

I've inserted the Application.ScreenUpdating=False statement into the w/s event and in the RepeatOneSec() macro with no avail!

Is there a VBA statement or function I can use to suppress this "nauseating" w/s behaviour ?? or, Is it an avoidable consequence of using RepeatOneSec() ?

Regards.