Blinking Cell (Worksheet restarts)

[FloggingDolphin]

Hi there,

I've copied and pasted some VBA code (can't remember which free site I got it from now) which gives me a blinking cell. However, each time I close the worksheet it re-opens itself.i've tried to add something to prevent it from doing that but i just get an error when i quit the worksheet. I can only stop it from re-opening if I disable the macros.

Could someone please assist, I'd gladly appreciate! The VBA code is:
-------------(for this workbook)-----------


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime BlinkTime, "Blink", , False

End Sub

Private Sub Workbook_Open()

Call Blink

End Sub





-------------(in a module)----------------

Dim BlinkTime As Date

Public Sub Blink()
Dim c As Range

For Each c In Range("C3").Cells
If c.Interior.ColorIndex = 47 Then
If c.Value > 100 Then
c.Interior.ColorIndex = 2 'White
ElseIf c.Value > 50 Then
c.Interior.ColorIndex = 2 'White
Else
c.Interior.ColorIndex = 2 'White
End If
Else
c.Interior.ColorIndex = 47
End If
Next c


BlinkTime = Now() + TimeValue("00:00:01")
Application.OnTime BlinkTime, "Blink"
End Sub

 

[bhawane]

still havent worked out why it keeps re-opening - but the sure fire way of closing is

Private Sub Workbook_BeforeClose(Cancel As Boolean)
application.quit
End Sub

 

[FloggingDolphin]

Thanks, I've tried that and it kinda helps but it closes all my other worksheets too. At least it's a step in the right direction though heh...

 

[bhawane]

ok got the solution for you.

Module1

Sub Blink(MyStart, MyFinish)
Dim c As Range
For Each c In Range("C3").Cells
If c.Interior.ColorIndex = 47 Then
If c.Value > 100 Then
c.Interior.ColorIndex = 2 'White
ElseIf c.Value > 50 Then
c.Interior.ColorIndex = 2 'White
Else
c.Interior.ColorIndex = 2 'White
End If
Else
c.Interior.ColorIndex = 47
End If

Next c
Application.OnTime MyStart, "Blink", MyFinish
End Sub

...............................................................

ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MyStart = Now()
MyFinish = Now()
Call Blink(MyStart, MyFinish)
End Sub

Private Sub Workbook_Open()
MyStart = Now() + TimeValue("00:00:01")
MyFinish = Now() + TimeValue("23:00:00")
Call Blink(MyStart, MyFinish)
End Sub

 

[FloggingDolphin]

I’ve tried adding your code and come up with something like this:

----Module1---

Sub Blink(MyStart, MyFinish)
Dim c As Range
For Each c In Range("C3").Cells
If c.Interior.ColorIndex = 47 Then

c.Interior.ColorIndex = 2 'White

Else
c.Interior.ColorIndex = 47


End If

Next c

Application.OnTime MyStart, "Blink", MyFinish
End Sub



...............................................................

---ThisWorkbook---

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MyStart = BlinkTime = Now()
MyFinish = BlinkTime = Now()
Call Blink(MyStart, MyFinish)
End Sub

Private Sub Workbook_Open()
MyStart = BlinkTime = Now() + TimeValue("00:00:01")
MyFinish = BlinkTime = Now() + TimeValue("23:00:00")
Call Blink(MyStart, MyFinish)
End Sub


I have tried applying it and I end up getting cell C3 changing colour only once. The only other it changes is when I’m about to quit (ie. It asks me if I want to save or not).

I think it might be missing something…

 

[bhawane]

Have you resolved your blinking cell problem yet?

I have had another idea as to what you can do. Something I had a problem with a while ago, I was creating a pivot table and eveytime a new sheet opened I needed to have it formatted, so I got the macro to write another macro, in the same sense you could get a macro in Workbook open to write the macro to run the blinking cell, then on close get it to delete the macro again.

I'm not entirely sure how to do this, and would like to use this method if you have any ideas.

 

[FloggingDolphin]

hmm....all i can think of is recording the whole formatting program and running the macro each time a new work sheet is added.

i'm sure there's some vba code that states 'IF new worksheet added, run macro "xxx"'

try using the help assistant i guess...

sorry i couldnt help much.

fd

 

[KL]

Hi guys,

I couldn't find the begining of this thread in Google, but have you
considered the following solution. It has the advantage of preserving the
undo functionality of Excel and the disadvantage of a constantly running
code.

Regards,
KL

1) Create a named formula called TIMER
=MOD(SECOND(NOW()),2)=1

2) Select the range you want to blink subject to a condition (or multiple
conditions), say range A1:A100

3) Apply the following Conditional Format
=TIMER*(A1<0)

where (A1<0) is one of the many possible conditions.

4) put the following code into the VBA module of ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlinking
End Sub

Private Sub Workbook_Open()
StartBlinking
End Sub

5) put the following code into a standard module (say Module1):

Dim dtNext As Date

Sub StartBlinking()
dtNext = Now + TimeValue("00:00:01")
Application.Calculate
Application.OnTime dtNext, "StartBlinking"
End Sub

Sub StopBlinking()
Application.OnTime dtNext, "StartBlinking", schedule:=False
End Sub

 

[FloggingDolphin]

kudos KL,

but does that mean that my workbook would still restart when i quit it?


thanks,
fd

 

[KL]

I'd be surprised if it did

KL

 

[FloggingDolphin]

hey there, thanks for that advice. Can you just teach me how to name a formula called TIMER. Is that a Macro or something? I tried to check excel help on how to create and name formulae but failed.

thanks
fd

 

[KL]

Hi FloggingDolphin,

This is [almost] exactly the same as a named range. Essentially when you
create a named range you create a named formula as you enter something like
=A1 which is a formula refering to a range. So the step-by-step is:

1) on the spreadsheet window go to menu Insert>Name>Define
2) enter "Timer" (w/o quatation marks) into the 'Names in workbook' box
3) enter =MOD(SECOND(NOW()),2)=1 into the 'Refers to:' box
4) press Add, OK

Voila!

Regards,
KL


Regards,
Kl

 

[FloggingDolphin]

I’ve tried it out and it works. Thanks!

One other thing. I’d like it to Stopblink after I input a value between 1 and 13 in the cell (C1). This is in order to prevent it to constantly re-calculating and making my whole spreadsheet slightly blink.

I’d also like to Start blink should my cell (C1) become clear again.

If it's alright, can you please tell me what VBA code to add to do the above?

Thanks again,

FD