RUN Macro Upon a Certain Cell Value Changing via a DDE App

[bfloyd360]

I'm currently writing or trying to write the code that connects a PLC to a pc and then capture data from the plc to the pc and insert the event in a worksheet on microsoft excel. It's works somewhat but here is my problem, when the cell value of AA10 changes on sheet one (either a 1 or 0 binary), I want the macro to log the time in one column and then display a text message in the next column to that time.

I will have this system connected to a conveyor line to log down time, so when the operator presses the E_Stop button at the first station (6 stations total can stop the line) it will log what time the operator pressed the button which changes the state of AA10 from 1 to 0. Then when the operator releases the stop button the value in cell AA10 changes from 0 to 1. Each time this occurs I'll have a message that shows in the next column which stop was pressed and when it was released.

Here is the code that I'm using now.

Private Sub Worksheet_Calculate()

'Code Below Logs The Stop Time For USDA Station #1 Stop Time.
'
If [AA10] = 0 Then
' Logs The Time That Station 1 Line 1 Stopped The Line
' Determines The Next Empty Row In Column A
Sheets(2).Range("A65536").End(xlUp).Offset(1, 0) = Time
'ActiveCell.NumberFormat = "h:mm:ss AM/PM"
' Logs The Description of The Event Next To The Time
' Determines The Next Empty Row In Column B
Sheets(2).Range("B65536").End(xlUp).Offset(1, 0) = "Station 1 Line 1 Stopped The Line"
End If
' Logs The Time That Station 1 Line 1 Released The Line
If [AA10] = 1 Then
Sheets(2).Range("A65536").End(xlUp).Offset(1, 0) = Time
'ActiveCell.NumberFormat = "h:mm:ss AM/PM"
' Logs The Description of The Event Next To The Time
Sheets(2).Range("B65536").End(xlUp).Offset(1, 0) = "Station 1 Line 1 Released The Line"

End If
End If
End Sub

The problem is when any cell is clicked it changes that cell to the time format that I'm using above. I know why because I'm using the active cell number format. I'm new at all this VBA and I'm not sure how to make that just a selected range not everything that is active.

2nd problem is when I added a another station monitoring another cell plus the one above it would run the entire code in the Private Sub Worksheet_Calculate() area. I just want to log the information if that state changes once from 1 to 0. If any of the other six stations change I don't want to know the status of the ones that are pressed. I just want to log when this ESTOP button is pressed (time hh:mm:ss) and when it was released (time hh:mm:ss). and insert this information in sheet two. When the stop time is inserted in A1 I want a text message inserted in to B1 that says "Station 1 Estop has Been Pressed" or something.

Is there some code that I can write just to watch a single cell then run some event or wait for a event.

Thanks!!!!

 

[Legacy 98055]

The worksheet change event will not fire for DDE events...

The fourth post in this link contains an example of using SetLinkOnData... It is pretty straight-forward. You will Create a procedure for each cell watched. Post back if you need more help...

http://www.mrexcel.com/board2/viewtopic.php?t=220456&highlight=dde

 

[bfloyd360]

Works Great!!!! But....

Ok it works great, but there is one small issue. When the value of A1 changes using the example from a 1 to a 0. My Data Sheet logs a 1 and when the state changes from a 0 to a 1 my data sheet logs a 0. I setup 2 inputs off the of the plc and was watching it. Here is the code below for the entire module 1 (one more question past the code)


Option Explicit

Private Const MyLinksName As String = "DSDATA|GKCOUNTER!X0"
Private Const MyLinksName1 As String = "DSDATA|GKCOUNTER!X1"
Private Const RecordToSheetName = "Data"

Sub BeginRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"
ThisWorkbook.SetLinkOnData MyLinksName1, "RecordUpdates1"
ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents
End Sub

Sub RecordUpdates()
Dim r As Long
' USDA Station 1 Line 1 ESTOP
With ThisWorkbook.Sheets(RecordToSheetName)
r = Application.WorksheetFunction.CountA(.Columns(1)) + 1
If r > 65535 Then StopRecordingUpdates
.Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value
.Cells(r, 2).Value = Time

End With
End Sub
Sub RecordUpdates1()
Dim r1 As Long
' USDA Station 2 Line 1 ESTOP
With ThisWorkbook.Sheets(RecordToSheetName)
r1 = Application.WorksheetFunction.CountA(.Columns(3)) + 1
If r1 > 65535 Then StopRecordingUpdates1
.Cells(r1, 3).Value = ThisWorkbook.Sheets("DDE_Link").Range("A2").Value
.Cells(r1, 4).Value = Time

End With
End Sub

Sub StopRecordingUpdates()
' For USDA Line 1 Station 1
ThisWorkbook.SetLinkOnData MyLinksName, ""
End Sub
Sub StopRecordingUpdates1()
' For USDA Line 1 Station 2
ThisWorkbook.SetLinkOnData MyLinksName1, ""
End Sub

Another question---
Here is the catch now. I'm logging the value of Cell A1 and A2 on the data sheet. Now, I want to write a message to that cell saying the following.

If the value in cell A1 = 0 then "Line 1 Station 1 Estop Called"
or
If the value in cell A1 = 1 then "Line 1 Station 1 Estop Released"

Thanks again for all your help.

 

[Legacy 98055]

"Now, I want to write a message to that cell saying the following."

I do not understand? You wish to replace the link it'self with text?

If possible, email me your workbook with expected behavior in cell comments...

Thanks.

 

[bfloyd360]

Text

I ended up using conditional formatting instead of text, I like it better. One more question, I want at the end of the shift for the operator to click a marco button and then I want the macro the file as a new workbook as the date and time as the file name. Then I would like to email the new work book saved as the date and time example (712065_54PM.xls) or something like that. It might be better if we save it as the date and day shift or night shift depending on the time. Let me know your thoughts on doing this. Everything else is working like charm. Thanks again for all your help.

I was thinking about building a templete and then when the operator clicks the button all the information is sent to a templete. The customer is not really sure what they would like to see, I think that is what is making this harder then it needs to be. They talked about if I could create a graph when I run the end of shift macro. I guess I could do it all?

Thanks again...
What is your email address and I'll send you a copy of the workbook in the morning. Thanks