Auto starting macros

I am monitoring a PLC output on my spreadsheet. Will say its in cell A1.
If the output is on, cell A1 displays a 1. If the output is off it displays a 0. Whenever A1 goes from 0 to 1, I log the time in cell D1 automatically. Then I drop it down one cell for every transition from 0 to 1.
My problem is I'm using 3 If-Then loop macros to do this.
1) a macro to put the time in and drop it down a cell. After it completes it starts the next macro.
2) the next macro waits for the transition from 1 to 0.
Sub MyFirstLoop
If Range("A1") = 1 then
Application.OnTime Now + TimeValue("00:00:02"), "MyFirstLoop"
Else: MySecondLoop
3) the last macro waits for the transition from 0 to 1.
Sub MySecondLoop
If Range("A1") = 0 then
Application.OnTime Now + TimeValue("00:00:02"), "MySecondLoop"
Else: MyTimeStamp 'run macro to put date into D1

A 2 second delay is about as high as I can go. This wouldn't be a problem, but I have about 50 outputs I'm monitoring. All of this looping slows down my system. Is there any instructions that will run the MyTimeStamp macro only when a 0 to 1 transition is noticed without having to constantly loop?
Thank you.

Why not with the "WorkSheet_Change" event ?

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value > 0 Then
MyTimeStamp
End If

End Sub

That will almost work Italkid. But not quite. You are going to be in a continous loop as soon as it turns from 0 to 1. Because as soon as you add the timestamp that's another change and A1 does > 0 so It will add timestamp on the next line, and A1 is still > 0. And .......

Just modify your code to check that Target.Address = Range("A1").Address before checking if A1 > 0.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Address = Target.Address Then
If Range("A1" ).Value > 0 Then
MyTimeStamp
End If
End If
End Sub

Yeah > diden't think about that. :whoops:

Ok but your code doesent start if Cellvalue will be changed thru a formula...
or do i got this wrong ?

Ok but your code doesent start if Cellvalue will be changed thru a formula...
or do i got this wrong ?
Correct, if the value is changed through a formula, then the Change event does not fire. Because the formula itself didn't change only the value displayed. It really depends on how he has his worksheet set up.

Did a little experiment

Option Explicit
Dim Val As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Val = Range("A1").Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value > 0 Or Range("A1").Address = Target.Address Then
If Val = 1 Or Target.Value = Val Then GoTo Einde
MyTimeStamp
End If

Einde:
End Sub

This way the code will fire if ("A1").Value gets changed thru a formula or thru input.
When ("A1").Value = 1 you can do any iput in the sheets whitout MyTimeStamp starts

Just some brainstorming because i don't know wich way the value changes in EricM's sheet.

Thanks for the quick reply guys. I tried it in a sample workbook and I couldn't get it to work. I'm not much of an excel genius either. You might have to dumb up you replies.
The input comes from a DDE server. Without operator interaction cell A1 will change from 0 to 1 and 1 to 0. The MyTimeStamp parts is:

Sub MyTimeStamp()
Range("D1") = Now()
Range("D1").Insert Shift:=xlDown
End Sub

I've never used the (ByVal Target As Range) before. Only ().

When I put it in Wamphyri's first code it just puts the date in when I run it the first time.
I'm trying to get the macro to run when A1 changes from 0 to 1 so I can log times that this transition occurs without having to monitor the spreadsheet.

Can I make a custom function that will run MyTimeStamp once when 1 is seen in A1 then reset when A1=0?

Can I make a custom function that will run MyTimeStamp once when 1 is seen in A1 then reset when A1=0?


Hi, I think the worksheet calculation event will do the job.


Dim Stamped As Boolean

Private Sub Worksheet_Calculate()
If Not Stamped And Cells(1, 1) = 1 Then
Stamped = True
Application.EnableEvents = False 'prevent recursive calls to calculation
MyTimeStamp
Application.EnableEvents = True 'always set back to TRUE
Else: Stamped = False
End If
If Cells(1, 1) = 1 Then Stamped = True 'set to TRUE anyway if A1 = 1
End Sub


Not tested, but I think it should work.

To force sheet to calculate when A1 is changed a add formula to any cell in worksheet that references A1.

ie: =A1

Hope this helps.

This could be a way to do it :
In your Vba editor select the correct worksheet in the project explorer.
Then in the left dropdown box above in the Vba editor select "WorkSheet".
In the right dropdown box select "Change" and after that "Selection_Change".
Copy and paste the code in to the correct places.

Option Explicit
Dim Val As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Val = Range("A1").Value 'stores the actual cell value > 1 or 0

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("A1").Address Then 'code will only run if Cell = "A1" > see Wamphyri's reply
If Range("A1").Value <> Val Then 'if the new cell value is < or > then value stored as "Val" then go on
Application.EnableEvents = False 'prevent that we go in a continous loop (we gonna make again a change in the sheet > put "Now" in cell "A2")
Range("A2").Value = Now 'put date & time in cell "A2"
Range("A2").Insert Shift:=xlDown 'replace everything
Application.EnableEvents = True 'all is done and we can reset Enable.Events
End If
End If

End Sub

Thanks for the help guys. I tried something different. I made a custom function.

Function myDate(Tag)
If Tag = 1 Then
myDate = Now()
Else: myDate = ""
End If
End Function

In cell B1 I put the formula =myDate(A1)
When A1 is 1 the time is put into B1.

My new problem now is how to log several dates. I don't know how to get the answer to move down one box at time and not the equation.
Whenever A1 goes to one I get a date to pop into B1. If I could get the date to move to B2 and then keep shuffling down.

Thanks for the help guys. I tried something different. I made a custom function.

Function myDate(Tag)
If Tag = 1 Then
myDate = Now()
Else: myDate = ""
End If
End Function

In cell B1 I put the formula =myDate(A1)
When A1 is 1 the time is put into B1.

My new problem now is how to log several dates. I don't know how to get the answer to move down one box at time and not the equation.
Whenever A1 goes to one I get a date to pop into B1. If I could get the date to move to B2 and then keep shuffling down.

Hi again, your little function works well.

Using your function try adding this to the worksheet calculation event:

Private Sub Worksheet_Calculate()
If myDate([A1]) > "" Then Range("B1").End(xlDown).Offset(1, 0) = Now()
End Sub

I tried it looked OK.

or, if you want the most recent date in B1 try:

Private Sub Worksheet_Calculate()
If myDate([A1]) > "" Then
Application.EnableEvents = False
[B1].Insert Shift:=xlDown: [B1] = Now()
Application.EnableEvents = True
End If
End Sub

I'm sorry Insomniac, I'm retarded.
I put your Private Sub Worksheet_Calculate() below my function procedure and I can't get it to work. I even put a statement in the function to call Worksheet_Calculate and all I get is #VALUE! in cell B1 when A1 has a 1 in it.
I don't understand how Worksheet_Calculate would run automatically without me having to click on the run key or a hot key.
I don't know how to get the answer that is displayed in B1 to move and not the function itself.

I can get just the value to move down with this:

Sub MoveTime()

Range("B1").Copy
Range("B2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

End Sub

I still get the #VALUE! error when I try to incorporate it into the function.

I can get just the value to move down with this:

Sub MoveTime()

Range("B1").Copy
Range("B2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

End Sub

I still get the #VALUE! error when I try to incorporate it into the function.

I think the reason you are getting the error #VALUE is because your formula is relative not absolute. Try changing it to:

=myDate($A$1)

As for the calculation code, are you putting in it the sheet code or a module??? It has to go in the sheet code. Right click on the sheet tab and it should take you to the VBE with sheet code, paste the calculation event code in there.

P.S. the previous codes I gave will fire with any calculation event so if you have other things happening to the sheet try this one instead:

Dim Stamped As Boolean

Private Sub Worksheet_Calculate()
If Not Stamped And [A1] = 1 Then
Stamped = True
Application.EnableEvents = False
[B1].Insert Shift:=xlDown: [B1] = Now()
Application.EnableEvents = True
Else: Stamped = False
End If
If [A1] = 1 Then Stamped = True
End Sub

I ran some tests and it seemed OK but I cant alow for anything else you may be doing.

The code should trigure automatically whenever you sheet is calcutated so there is no need to 'run' or 'click' anything.

Thanks Insomniac for the time. I didn't know to put the code in the sheet code area. It works great. I just changed a small thing. I took the:

[B1].Insert Shift:=xlDown: [B1] = Now()

out of the code and put in:

i = [B1].Value
[B2] = i
[B2].Insert Shift:=xlDown

Yours shifted =myDate($A$1) down a cell every time.
I was able to pull the time out of B1 and then start dropping down the time.
Thanks your the help.

Do you know how to make Private Sub Worksheet_Calculate generic.
The Function procedure I can put in different cells, multiple times, but I can't figure out how to make the Worksheet_Calculate useful in multiple locations.

Hey, great to see a positive working response.

Your modification to my suggestion works fine, but you will find that assuming you are using my last code, it did not rely on the function - if you run it with column B empty it will shift the previous time down and put the current time to B1. Anyway not that important.

As for your new question:

"Do you know how to make Private Sub Worksheet_Calculate generic.
The Function procedure I can put in different cells, multiple times, but I can't figure out how to make the Worksheet_Calculate useful in multiple locations."

If you examine the code it is set up to run only if A1 has changed to a 1 from the last time the event trigured. ie: it specifically only looks at cell A1 and compares to variable Stamped.

The calulation event fires when any change to the sheet causes a formula to need recalculating, so it is "generic" by default, it is up to you to write code to evaluate the changes.

Just some tips (learnt the hard way)

1. The calculation event in my opinion is the hardest one to utilise efficiently and without ambiguities!

2. There is also 'Workbook_SheetCalculate(ByVal Sh As Object)' that can be utilsed in the 'ThisWorkbook' code to fire whenever any sheet is calculated (even harder to use).

3.If you have Tools>>Options>>Calculation set to manual your code wont necessarily be trigured when you want!

4.If you have other workbooks opened there is the possibility that your calulation code may be trigured when the workbook is not the active window, so you must be more specific with your code. ie: based on the last code and your modification it would read something like:-

Dim Stamped As Boolean
Dim i As String
Private Sub Worksheet_Calculate()
If Not Stamped And ThisWorkbook.Sheets("Sheet1").[A1] = 1 Then Stamped = True
Application.EnableEvents = False
i = ThisWorkbook.Sheets("Sheet1").[B1].Value
ThisWorkbook.Sheets("Sheet1").[B2] = i
ThisWorkbook.Sheets("Sheet1").[B2].Insert Shift:=xlDown
Application.EnableEvents = True
Else: Stamped = False
End If
If ThisWorkbook.Sheets("Sheet1").[A1] = 1 Then Stamped = True
End Sub

or use:

If ActiveWindow.Caption <> ThisWorkbook.Name Then Exit Sub

to not run code if not active window!

5. If you have Excel 97 and need to run code based on a validation dropdown selection you have to use the calculation event as the sheetchange event is not trigured in Excel97 by a validation change.

I think that with a bit of trial and error and use of the help files in VBE you will be able to sort out what you want. You obviously have very good problem solving skills. Its a bit hard for me to be any more specific with suggestions without knowing more about your full requirements for your application.

P.S. now that you know where to put the code, check out the other events that are trigured, ie:

Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Best regards Insomniac.

We'll probably be veterans on this forum, before I get this straightened out. I was planning on putting the formula in cell C1 and D1 and E1 etc., and check for a 0 or 1 in cell A2 and A3 and A4 etc.
Would I need to put a bunch of If-then statements, to reference each new column, in the Worksheet_Calculation procedure or can I put something besides [A1],[B1], or [B2] that would make it "fire" if the formula was in any column.

OK, in short yes, you would have to assign a variable to store the state of each cell in column A that you may or may not alter. The real problem is that there is not a BEFORE CALCULATION or BEFORE SHEETCHANGE event.

To be honest it would probably be easier to utilise the routine that updates your workbook and get it call the relevant code within the workbook that can display your requirements.

You might also be able to create an array to store many rows of column A data and use SELECT CASE to determine what has been altered.

This could become quite complex, and once again I am only guessing as to your total objective.

A good learning curve though LOL, I have enjoyed the 'Works great but'

P.S. keep in mind that Im no expert and actually do a bit of research and rough test before each reply!

Thanks for the reply. You got me in the right direction. I just need to do a lot of trial and error.