Hi,
Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition. Any solution ideas are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Hi
though this is possible with some VBA code (using the OnTime method) I
really would recommend NOT to use such things:
- it will slow down Excel (due to zthe use of the OnTime method)
- personally I think blinking cells shouldn't be a part of a
spreadsheet

If you need this you may search the Google archives for blinking cells


--
Regards
Frank Kabel
Frankfurt, Germany


Martyn wrote:
Hi,
Can we achieve this via plain formula or VBA?. I need to have a
custom
conditional formatting (blinking background colour or highlighting
going on/off) for cells that fulfill a certain condition. Any
solution ideas are most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Some people around here have some unexplained hatred of blinking cells.
Often colour and formatting overuse accompanies bad spreadsheet design. To
me these are separate issues, so here's how to get blinking...

Check out my website for "Schedule a macro to run"

Include something like:
Sub RunMe()
With Range("A1").Font
If .ColorIndex = 3 Then .ColorIndex = 4 Else .ColorIndex = 3
End With
ScheduleNextEvent
End Sub

or if you want the background to blink: With Range("A1").Interior

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition. Any solution ideas are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Hi Rob
I'm one of these people <vbg
Just some comments:
- Blinking currently requires the usage of the OnTime method. So
there's a macro running constantly in the background. You also loose
the ability of 'Undo' your manuall actions in this case
- You also have to include a 'Stop' macro
- In addition to your code you should add a specific worksheet
reference. Otherwise changing the active sheets will result that A1
blinks also on the new active sheet

Personal thought: As this feature is requested quite often MS wll
probably include this in a furutre Excel release ;-)



--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
Some people around here have some unexplained hatred of blinking
cells. Often colour and formatting overuse accompanies bad
spreadsheet design. To me these are separate issues, so here's how to
get blinking...

Check out my website for "Schedule a macro to run"

Include something like:
Sub RunMe()
With Range("A1").Font
If .ColorIndex = 3 Then .ColorIndex = 4 Else .ColorIndex = 3
End With
ScheduleNextEvent
End Sub

or if you want the background to blink: With Range("A1").Interior


"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a
custom conditional formatting (blinking background colour or
highlighting going on/off) for cells that fulfill a certain
condition. Any solution ideas are most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Thanks for the comments of Frank's and code suggestion from Rob.
I think I'd do a search on coding alternatvies for this and see what the
performance will be like. Cause I "need" such an event in one of my XL
applications even if it takes only a few "blinks". I am not in need of
continous blinking of a cell(s). Thanks for the support given to me on this
two NGs so far. Thank you guys...


"Rob van Gelder" wrote in message
...
Some people around here have some unexplained hatred of blinking cells.
Often colour and formatting overuse accompanies bad spreadsheet design. To
me these are separate issues, so here's how to get blinking...

Check out my website for "Schedule a macro to run"

Include something like:
Sub RunMe()
With Range("A1").Font
If .ColorIndex = 3 Then .ColorIndex = 4 Else .ColorIndex = 3
End With
ScheduleNextEvent
End Sub

or if you want the background to blink: With Range("A1").Interior

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition. Any solution ideas
are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004






---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004


---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Hi!

I agree with Frank!

At least this approach only flashes if there's a change to the
offending cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("MyFlashCell")) Is Nothing Then Exit Sub
Dim n As Integer
Dim NextTime As Date
If Range("MyFlashCell").Value 7 Then
For n = 1 To 5
With Range("MyFlashCell").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
With Range("MyFlashCell").Interior
If .ColorIndex = 3 Then .ColorIndex = 2 Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next
End If
With Range("MyFlashCell")
..Font.ColorIndex = 3
..Interior.ColorIndex = 2
End With
End Sub

But don't do it unless you are really into annoying people who use the
workbook.

And As this feature is requested quite often MS wll probably
include this in a furutre Excel release ;-)

I hope that they add a couple of beeps to it and provide some fluffy
dice to hang in front of the VDU.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

"Frank Kabel" wrote in message
...
Hi Rob
I'm one of these people <vbg
Just some comments:
- Blinking currently requires the usage of the OnTime method. So
there's a macro running constantly in the background. You also loose
the ability of 'Undo' your manuall actions in this case
- You also have to include a 'Stop' macro
- In addition to your code you should add a specific worksheet
reference. Otherwise changing the active sheets will result that A1
blinks also on the new active sheet

Personal thought: As this feature is requested quite often MS wll
probably include this in a furutre Excel release ;-)



--
Regards
Frank Kabel
Frankfurt, Germany

I'm a developer, not a user. I'll never build blinking into my spreadsheets
so it's not really that much an issue for me.

There's a stop macro already - perhaps you missed it.
Good point about the undo and sheet references. Undo is certainly an issue
I'm not prepared to find a solution to.



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Frank Kabel" wrote in message
...
Hi Rob
I'm one of these people <vbg
Just some comments:
- Blinking currently requires the usage of the OnTime method. So
there's a macro running constantly in the background. You also loose
the ability of 'Undo' your manuall actions in this case
- You also have to include a 'Stop' macro
- In addition to your code you should add a specific worksheet
reference. Otherwise changing the active sheets will result that A1
blinks also on the new active sheet

Personal thought: As this feature is requested quite often MS wll
probably include this in a furutre Excel release ;-)



--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
Some people around here have some unexplained hatred of blinking
cells. Often colour and formatting overuse accompanies bad
spreadsheet design. To me these are separate issues, so here's how to
get blinking...

Check out my website for "Schedule a macro to run"

Include something like:
Sub RunMe()
With Range("A1").Font
If .ColorIndex = 3 Then .ColorIndex = 4 Else .ColorIndex = 3
End With
ScheduleNextEvent
End Sub

or if you want the background to blink: With Range("A1").Interior


"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a
custom conditional formatting (blinking background colour or
highlighting going on/off) for cells that fulfill a certain
condition. Any solution ideas are most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Hi Martyn, the code below will allow the text to blink as many times
as you would like, just adjust to suit...Cells(1, 1)= A1... in case you
didn't know....

Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition.

Public Sub fla****()
ActiveSheet.Cells(1, 1) = "look"
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = ""
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = "look"
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = ""
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = "look"
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = ""
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = "look"
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = ""
Application.Wait (Now + TimeValue("0:00:01"))
ActiveSheet.Cells(1, 1) = "look"
Application.Wait (Now + TimeValue("0:00:01"))
End Sub

Hope this helps...

seeya ste

Frank Kabel, Rob van Gelder, Norman harker and ste mac,
Thank you for all your suggestions and alternatives on topic. I gathered the
codes from all of you, did a google search on the topic and tried to
classify the situations. End up with 3 different cathegories. I have u/l' ed
the bunch to my website as macro examples on flashing but unfortunately it
ain't english. Thus if anybody interested do let me know...
Sincerely
Martyn
--
¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
A friend in need, is a friend indeed...
Web: http://www.eserceker.com
Email:
___Zoom©
¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Hi Rob

didn't look at your website. My comment regarding the Stop macro was
just based on your posting :-)

My problem with these blinking cells come from bad experience. I once
provided a colleague of mine with this kind of macro just to proof it's
possible. Sme weeks later he sent me a sheet which was blinking all
over <vbg

For the Undo part: I don't think there's an easy solution available. Of
course you can code your own undo prcoedures but this would require
some effort


--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
I'm a developer, not a user. I'll never build blinking into my
spreadsheets so it's not really that much an issue for me.

There's a stop macro already - perhaps you missed it.
Good point about the undo and sheet references. Undo is certainly an
issue I'm not prepared to find a solution to.

"Martyn" wrote in message ...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition. Any solution ideas are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Martyn!

Here is my VBA-module "Timer" with two different ways using
Excel-Timer and Windows-Timer. Please post further quesions here.

Dirk



Option Explicit

Private Declare Function SetTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As
Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long) As Long

Private timExcelTimerOff As Boolean
Private timWindowsTimerId As Long
Private timWindowsTimerOn As Boolean

Public Function timStartWindowsTimer()
' Der Timer darf nur einmal gestartet werden!
If Not timWindowsTimerOn Then
timWindowsTimerId = SetTimer(0&, 0&, 100&, AddressOf
timWindowsTimerEvent)
timWindowsTimerOn = True
End If
End Function

Public Function timStopWindowsTimer()
On Error Resume Next
If timWindowsTimerOn Then
KillTimer 0&, timWindowsTimerId
timWindowsTimerOn = False
End If
End Function

Public Function timWindowsTimerEvent(ByVal HWnd As Long, ByVal uMsg As
Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error Resume Next
timCustomTimerEvent
End Function

Public Function timStartExcelTimer()
Dim timNextTime As Double
timExcelTimerOff = False
timNextTime = Now + TimeSerial(0, 0, 1)
Application.OnTime timNextTime, "timExcelTimerEvent", False
End Function

Public Function timStopExcelTimer()
' Wie kann das Excel-Timer-Ereignis so gelöscht werden,
' dass es auch vom Workbook_BeforeClose aus funktioniert?
timExcelTimerOff = True
End Function

Public Function timExcelTimerEvent()
If Not timExcelTimerOff Then
timCustomTimerEvent
timStartExcelTimer
End If
End Function

Public Function timCustomTimerEvent()
Dim vCell As Range
Set vCell = WS1.Cells(1, 1)
If vCell.Interior.ColorIndex = 6 Then
vCell.Interior.ColorIndex = 4
Else
vCell.Interior.ColorIndex = 6
End If
WS1.Cells(1, 1) = Time
End Function

<<<

Thank you Dirk...

"Dirk Kampfmeier" wrote in message
om...
"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition. Any solution ideas
are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Martyn!

Here is my VBA-module "Timer" with two different ways using
Excel-Timer and Windows-Timer. Please post further quesions here.

Dirk



Option Explicit

Private Declare Function SetTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As
Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long) As Long

Private timExcelTimerOff As Boolean
Private timWindowsTimerId As Long
Private timWindowsTimerOn As Boolean

Public Function timStartWindowsTimer()
' Der Timer darf nur einmal gestartet werden!
If Not timWindowsTimerOn Then
timWindowsTimerId = SetTimer(0&, 0&, 100&, AddressOf
timWindowsTimerEvent)
timWindowsTimerOn = True
End If
End Function

Public Function timStopWindowsTimer()
On Error Resume Next
If timWindowsTimerOn Then
KillTimer 0&, timWindowsTimerId
timWindowsTimerOn = False
End If
End Function

Public Function timWindowsTimerEvent(ByVal HWnd As Long, ByVal uMsg As
Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error Resume Next
timCustomTimerEvent
End Function

Public Function timStartExcelTimer()
Dim timNextTime As Double
timExcelTimerOff = False
timNextTime = Now + TimeSerial(0, 0, 1)
Application.OnTime timNextTime, "timExcelTimerEvent", False
End Function

Public Function timStopExcelTimer()
' Wie kann das Excel-Timer-Ereignis so gelöscht werden,
' dass es auch vom Workbook_BeforeClose aus funktioniert?
timExcelTimerOff = True
End Function

Public Function timExcelTimerEvent()
If Not timExcelTimerOff Then
timCustomTimerEvent
timStartExcelTimer
End If
End Function

Public Function timCustomTimerEvent()
Dim vCell As Range
Set vCell = WS1.Cells(1, 1)
If vCell.Interior.ColorIndex = 6 Then
vCell.Interior.ColorIndex = 4
Else
vCell.Interior.ColorIndex = 6
End If
WS1.Cells(1, 1) = Time
End Function

<<<


---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.704 / Virus Database: 460 - Release Date: 12.06.2004


---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.704 / Virus Database: 460 - Release Date: 12.06.2004

Here is my 2c worth Martyn.


Sub GoneIn2Sec()
Dim t As Double, Rng As Range
Dim tt As Double
Set Rng = ActiveCell
Application.EnableCancelKey = xlErrorHandler
On Error GoTo End1
If [B1] = "" Or Not IsNumeric([B1]) Then
tt = Timer + 30
Else
tt = Timer + [B1].Value
End If
With Rng
Do While Timer < tt
If .Interior.ColorIndex < 3 Then
.Interior.ColorIndex = 3
.Font.Bold = True
Beep
Else
.Interior.ColorIndex = Int(Rnd() * 56 + 1)
.Font.Bold = False
End If
'the 0.16 lets one blink faster than a second
t = Timer + 0.16
Do While Timer < t
DoEvents
Loop
Loop
End With
End1:
End Sub


By modifying the 0.16 you can increase or decrease the blinking rate.
To stop this early just use Ctrl + Break, this won't take you to the VBE.
It blinks for 30 seconds with a beep for each blink. Enter a smaller value in B1 for shorter blinking duration.
And it is self-contained without needing any other function or macro. You can even select other cells or change to another
application while blinking and beeping - XL that is. <bg

Although if you do try to edit a cell the code will just stop.


Regards
Robert McCurdy

"Martyn" wrote in message ...
Thank you Dirk...

"Dirk Kampfmeier" wrote in message
om...
"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a custom
conditional formatting (blinking background colour or highlighting going
on/off) for cells that fulfill a certain condition. Any solution ideas
are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Martyn!

Here is my VBA-module "Timer" with two different ways using
Excel-Timer and Windows-Timer. Please post further quesions here.

Dirk



Option Explicit

Private Declare Function SetTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As
Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long) As Long

Private timExcelTimerOff As Boolean
Private timWindowsTimerId As Long
Private timWindowsTimerOn As Boolean

Public Function timStartWindowsTimer()
' Der Timer darf nur einmal gestartet werden!
If Not timWindowsTimerOn Then
timWindowsTimerId = SetTimer(0&, 0&, 100&, AddressOf
timWindowsTimerEvent)
timWindowsTimerOn = True
End If
End Function

Public Function timStopWindowsTimer()
On Error Resume Next
If timWindowsTimerOn Then
KillTimer 0&, timWindowsTimerId
timWindowsTimerOn = False
End If
End Function

Public Function timWindowsTimerEvent(ByVal HWnd As Long, ByVal uMsg As
Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error Resume Next
timCustomTimerEvent
End Function

Public Function timStartExcelTimer()
Dim timNextTime As Double
timExcelTimerOff = False
timNextTime = Now + TimeSerial(0, 0, 1)
Application.OnTime timNextTime, "timExcelTimerEvent", False
End Function

Public Function timStopExcelTimer()
' Wie kann das Excel-Timer-Ereignis so gelöscht werden,
' dass es auch vom Workbook_BeforeClose aus funktioniert?
timExcelTimerOff = True
End Function

Public Function timExcelTimerEvent()
If Not timExcelTimerOff Then
timCustomTimerEvent
timStartExcelTimer
End If
End Function

Public Function timCustomTimerEvent()
Dim vCell As Range
Set vCell = WS1.Cells(1, 1)
If vCell.Interior.ColorIndex = 6 Then
vCell.Interior.ColorIndex = 4
Else
vCell.Interior.ColorIndex = 6
End If
WS1.Cells(1, 1) = Time
End Function



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 11/06/2004

Hi Robert,
Your code introduces new components for me
i) using sound in harmony with blinking
ii) assigning a variable to modify timer settings
iii) adjustable blinking rate
I have no idea I could get that much of solution suggestions on "blinking".
Thank you very much all...
Martyn

"Robert McCurdy" wrote in message
...
Here is my 2c worth Martyn.


Sub GoneIn2Sec()
Dim t As Double, Rng As Range
Dim tt As Double
Set Rng = ActiveCell
Application.EnableCancelKey = xlErrorHandler
On Error GoTo End1
If [B1] = "" Or Not IsNumeric([B1]) Then
tt = Timer + 30
Else
tt = Timer + [B1].Value
End If
With Rng
Do While Timer < tt
If .Interior.ColorIndex < 3 Then
.Interior.ColorIndex = 3
.Font.Bold = True
Beep
Else
.Interior.ColorIndex = Int(Rnd() * 56 + 1)
.Font.Bold = False
End If
'the 0.16 lets one blink faster than a second
t = Timer + 0.16
Do While Timer < t
DoEvents
Loop
Loop
End With
End1:
End Sub


By modifying the 0.16 you can increase or decrease the blinking rate.
To stop this early just use Ctrl + Break, this won't take you to the VBE.
It blinks for 30 seconds with a beep for each blink. Enter a smaller
value in B1 for shorter blinking duration.
And it is self-contained without needing any other function or macro. You
can even select other cells or change to another
application while blinking and beeping - XL that is. <bg

Although if you do try to edit a cell the code will just stop.


Regards
Robert McCurdy

"Martyn" wrote in message
...
Thank you Dirk...

"Dirk Kampfmeier" wrote in message
om...
"Martyn" wrote in message
...
Hi,
Can we achieve this via plain formula or VBA?. I need to have a
custom
conditional formatting (blinking background colour or highlighting
going
on/off) for cells that fulfill a certain condition. Any solution
ideas
are
most wellcomed.
TIA



---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 10.06.2004

Martyn!

Here is my VBA-module "Timer" with two different ways using
Excel-Timer and Windows-Timer. Please post further quesions here.

Dirk



Option Explicit

Private Declare Function SetTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As
Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal HWnd As Long,
ByVal nIDEvent As Long) As Long

Private timExcelTimerOff As Boolean
Private timWindowsTimerId As Long
Private timWindowsTimerOn As Boolean

Public Function timStartWindowsTimer()
' Der Timer darf nur einmal gestartet werden!
If Not timWindowsTimerOn Then
timWindowsTimerId = SetTimer(0&, 0&, 100&, AddressOf
timWindowsTimerEvent)
timWindowsTimerOn = True
End If
End Function

Public Function timStopWindowsTimer()
On Error Resume Next
If timWindowsTimerOn Then
KillTimer 0&, timWindowsTimerId
timWindowsTimerOn = False
End If
End Function

Public Function timWindowsTimerEvent(ByVal HWnd As Long, ByVal uMsg As
Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error Resume Next
timCustomTimerEvent
End Function

Public Function timStartExcelTimer()
Dim timNextTime As Double
timExcelTimerOff = False
timNextTime = Now + TimeSerial(0, 0, 1)
Application.OnTime timNextTime, "timExcelTimerEvent", False
End Function

Public Function timStopExcelTimer()
' Wie kann das Excel-Timer-Ereignis so gelöscht werden,
' dass es auch vom Workbook_BeforeClose aus funktioniert?
timExcelTimerOff = True
End Function

Public Function timExcelTimerEvent()
If Not timExcelTimerOff Then
timCustomTimerEvent
timStartExcelTimer
End If
End Function

Public Function timCustomTimerEvent()
Dim vCell As Range
Set vCell = WS1.Cells(1, 1)
If vCell.Interior.ColorIndex = 6 Then
vCell.Interior.ColorIndex = 4
Else
vCell.Interior.ColorIndex = 6
End If
WS1.Cells(1, 1) = Time
End Function



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 11/06/2004




---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.704 / Virus Database: 460 - Release Date: 12.06.2004


---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.704 / Virus Database: 460 - Release Date: 12.06.2004