good morning,

I have a big excel spread with a lot of different vba functions and
volitale functions

I would just like to add function that in a specified cell every second the
value of the cell is increasing by 1.

My problem is that as soon I add the function


Private Sub Worksheet_Calculate()
Application.OnTime Now + TimeValue("00:00:01"), "zahl"
End Sub


Public i

Sub zahl()
i = i + 1
Cells(5, 5) = i
End Sub

The spreadsheet is not updating every second but maybe every millisecond

Is there a way that the Worksheet_Calculate() is only trigger if the
specified cell is changed ??

Best Regards
Ciao
Martin

Hi Martin,

with Private Sub Worksheet_Change(ByVal Target As Range)
you cant use
If Target.Address = "$A$10" Then '<<<< change cell

to accomplish what you are looking for ...

HTH
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=531291

The problem is using Calculate, as it fires a new Ontime each time. Why not
just run a macro from workbook open that fires Ontime, and that macro resets
the Ontime call. C hip shows how at http://www.cpearson.com/excel/ontime.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin Bauer" wrote in message
0...
good morning,

I have a big excel spread with a lot of different vba functions and
volitale functions

I would just like to add function that in a specified cell every second
the
value of the cell is increasing by 1.

My problem is that as soon I add the function


Private Sub Worksheet_Calculate()
Application.OnTime Now + TimeValue("00:00:01"), "zahl"
End Sub


Public i

Sub zahl()
i = i + 1
Cells(5, 5) = i
End Sub

The spreadsheet is not updating every second but maybe every millisecond

Is there a way that the Worksheet_Calculate() is only trigger if the
specified cell is changed ??

Best Regards
Ciao
Martin

You should not use the Calculate Event (which may happen a any time)
You'd rather use Workbook_open to initialize the process.

Paste this in ThisWorkbook code:

'---------------------------------------------
Private Sub Workbook_Open()
zahl
End Sub
'---------------------------------------------

Then paste into a Module:
'----------------------------------
Sub zahl()
Static i
i = i + 1
Cells(5, 5).Value = i
' Reinit timer
Application.OnTime Now + TimeValue("00:00:01"), "zahl"
End Sub
'------------------------------------

HTH
--
AP



You should start your (time) event handler from Workbook_open event

"Martin Bauer" a écrit dans le message de
0...
good morning,

I have a big excel spread with a lot of different vba functions and
volitale functions

I would just like to add function that in a specified cell every second
the
value of the cell is increasing by 1.

My problem is that as soon I add the function


Private Sub Worksheet_Calculate()
Application.OnTime Now + TimeValue("00:00:01"), "zahl"
End Sub


Public i

Sub zahl()
i = i + 1
Cells(5, 5) = i
End Sub

The spreadsheet is not updating every second but maybe every millisecond

Is there a way that the Worksheet_Calculate() is only trigger if the
specified cell is changed ??

Best Regards
Ciao
Martin

"Ardus Petus" wrote in
:

This was the solution - it is working extremly well

Thanks again
Ciao

You should not use the Calculate Event (which may happen a any time)
You'd rather use Workbook_open to initialize the process.

Paste this in ThisWorkbook code:

'---------------------------------------------
Private Sub Workbook_Open()
zahl
End Sub
'---------------------------------------------

Then paste into a Module:
'----------------------------------
Sub zahl()
Static i
i = i + 1
Cells(5, 5).Value = i
' Reinit timer
Application.OnTime Now + TimeValue("00:00:01"), "zahl"
End Sub
'------------------------------------

HTH
--
AP



You should start your (time) event handler from Workbook_open event

"Martin Bauer" a écrit dans le message de
0...
good morning,

I have a big excel spread with a lot of different vba functions and
volitale functions

I would just like to add function that in a specified cell every
second
the
value of the cell is increasing by 1.

My problem is that as soon I add the function


Private Sub Worksheet_Calculate()
Application.OnTime Now + TimeValue("00:00:01"), "zahl"
End Sub


Public i

Sub zahl()
i = i + 1
Cells(5, 5) = i
End Sub

The spreadsheet is not updating every second but maybe every
millisecond

Is there a way that the Worksheet_Calculate() is only trigger if the
specified cell is changed ??

Best Regards
Ciao
Martin

"Bob Phillips" wrote in
:

The problem is using Calculate, as it fires a new Ontime each time.
Why not just run a macro from workbook open that fires Ontime, and
that macro resets the Ontime call. C hip shows how at
http://www.cpearson.com/excel/ontime.htm


Indeed I solved it this way

Thanks

Ciao

Carim wrote in
:


Hi Martin,

with Private Sub Worksheet_Change(ByVal Target As Range)
you cant use
If Target.Address = "$A$10" Then '<<<< change cell

to accomplish what you are looking for ...

HTH
Carim


hi martin,

I solved it via worksheet_open

Cheers
thanks
M