Macro Running Twice

[Guest]

Hi

I have a Macro that is run from a button, the macro, when running adds data
to the next line in a column every second, however, if the button is pressed
twice, two lines of the same data are added every second, it appears that the
macro is running twice.

Is there any way I can have the macro running once.

Best Regards
Alec

 

[Guest]

Very probably but until you post the code it's doubtful anyone can give too
much help.

Mike

 

[Guest]

Hi Mike
Thanks for your reply, the macro code is as follows:
I then use Workshheet_Calculate to update cells every second

Sub STARTTIMER()
Start = Timer()
TimeEvent
End Sub
Sub TimeEvent()
Sheets("Timer").[BB1] = Format((Timer() - Start) / 3600 / 24, "hh:mm:ss")
SecondTimer = Now + TimeValue("00:00:1")
Application.OnTime SecondTimer, "TimeEvent"
End Sub

 

[Guest]

Hi,

The macto as posted will write the time to BB1 every second and there's
nothing wrong with the way it works. You mention worksheet_calculate, how are
you calling that because updating the time won't.

Mike

Hi Mike
Thanks for your reply, the macro code is as follows:
I then use Workshheet_Calculate to update cells every second

Sub STARTTIMER()
Start = Timer()
TimeEvent
End Sub
Sub TimeEvent()
Sheets("Timer").[BB1] = Format((Timer() - Start) / 3600 / 24, "hh:mm:ss")
SecondTimer = Now + TimeValue("00:00:1")
Application.OnTime SecondTimer, "TimeEvent"
End Sub

Very probably but until you post the code it's doubtful anyone can give too
much help.

Mike

 

[Guest]

Thanks for your reply Mike,
The updates are as follows, and change when ever there is a value change on
the sheet, this works fine until the macro button is pressed twice or more


Private Sub Worksheet_Calculate()
Dim iLastRow As Long
iLastRow = Range("D65536").End(xlUp).Row
Range("A1").Value = Range("A1").Value + 1
Range("A2").Value = Range("A1").Value / 60
Range("A3").Value = Range("A2").Value / 60
If Cells(iLastRow, 2).Value <> Range("A1").Value Then
Cells(iLastRow + 1, 4).Value = Range("A1").Value
Cells(iLastRow + 1, 5).Value = Range("A6").Value
Cells(iLastRow + 1, 6).Value = Range("A13").Value
Cells(iLastRow + 1, 7).Value = Range("A14").Value
Cells(iLastRow + 1, 8).Value = Range("A15").Value
Cells(iLastRow + 1, 9).Value = Range("A16").Value
Cells(iLastRow + 1, 10).Value = Range("A17").Value
Cells(iLastRow + 1, 11).Value = Range("A18").Value
End If
End Sub

Hi,

The macto as posted will write the time to BB1 every second and there's
nothing wrong with the way it works. You mention worksheet_calculate, how are
you calling that because updating the time won't.

Mike

Hi Mike
Thanks for your reply, the macro code is as follows:
I then use Workshheet_Calculate to update cells every second

Sub STARTTIMER()
Start = Timer()
TimeEvent
End Sub
Sub TimeEvent()
Sheets("Timer").[BB1] = Format((Timer() - Start) / 3600 / 24, "hh:mm:ss")
SecondTimer = Now + TimeValue("00:00:1")
Application.OnTime SecondTimer, "TimeEvent"
End Sub

Very probably but until you post the code it's doubtful anyone can give too
much help.

Mike

:

Hi

I have a Macro that is run from a button, the macro, when running adds data
to the next line in a column every second, however, if the button is pressed
twice, two lines of the same data are added every second, it appears that the
macro is running twice.

Is there any way I can have the macro running once.

Best Regards
Alec

 

[Guest]

I found this unanswered thread while trying to solve a similar problem.

I have several OnTime events in two workbooks that copy/paste/delete from
each other and had the same problem of the macros seeming to run repetitively.

I added a timer stop to the beginning of each macro and a timer start at the
end. The problem seems to be gone now, but was this really the answer or was
there more likely a looping problem (If/Thens, I've done additional editing
since adding the timer stop/starts)?

--
n00b lookn for a handout

Thanks for your reply Mike,
The updates are as follows, and change when ever there is a value change on
the sheet, this works fine until the macro button is pressed twice or more


Private Sub Worksheet_Calculate()
Dim iLastRow As Long
iLastRow = Range("D65536").End(xlUp).Row
Range("A1").Value = Range("A1").Value + 1
Range("A2").Value = Range("A1").Value / 60
Range("A3").Value = Range("A2").Value / 60
If Cells(iLastRow, 2).Value <> Range("A1").Value Then
Cells(iLastRow + 1, 4).Value = Range("A1").Value
Cells(iLastRow + 1, 5).Value = Range("A6").Value
Cells(iLastRow + 1, 6).Value = Range("A13").Value
Cells(iLastRow + 1, 7).Value = Range("A14").Value
Cells(iLastRow + 1, 8).Value = Range("A15").Value
Cells(iLastRow + 1, 9).Value = Range("A16").Value
Cells(iLastRow + 1, 10).Value = Range("A17").Value
Cells(iLastRow + 1, 11).Value = Range("A18").Value
End If
End Sub

Hi,

I have a Macro that is run from a button, the macro, when running adds data
to the next line in a column every second, however, if the button is pressed
twice, two lines of the same data are added every second, it appears that the
macro is running twice.

The macto as posted will write the time to BB1 every second and there's
nothing wrong with the way it works. You mention worksheet_calculate, how are
you calling that because updating the time won't.

Mike

Hi Mike
Thanks for your reply, the macro code is as follows:
I then use Workshheet_Calculate to update cells every second

Sub STARTTIMER()
Start = Timer()
TimeEvent
End Sub
Sub TimeEvent()
Sheets("Timer").[BB1] = Format((Timer() - Start) / 3600 / 24, "hh:mm:ss")
SecondTimer = Now + TimeValue("00:00:1")
Application.OnTime SecondTimer, "TimeEvent"
End Sub

:

Very probably but until you post the code it's doubtful anyone can give too
much help.

Mike

:

Hi

I have a Macro that is run from a button, the macro, when running adds data
to the next line in a column every second, however, if the button is pressed
twice, two lines of the same data are added every second, it appears that the
macro is running twice.

Is there any way I can have the macro running once.

Best Regards
Alec