Calculate Event

[Martin Bauer]

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

[Carim]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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

[Martin Bauer]

"Ardus Petus" <[email protected]> wrote in
news:[email protected]:

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" <[email protected]> a écrit dans le message de
> news:[email protected]...
>> 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
>
>
>

[Martin Bauer]

"Bob Phillips" <[email protected]> wrote in
news:[email protected]:

> 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

[Martin Bauer]

Carim <[email protected]> wrote in
news:[email protected]:

>
> 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