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]

With Sheets(2).Range("A65536").End(xlUp).Offset(1, 0)
    .Value = Time
    .NumberFormat = "h:mm:ss AM/PM"
End With

Don't use the calculate event. It's usually a very poor workaround when working with DDE data...

See the following posts for some examples and general info...

DDE server data into excel, how to auto record those datas?
Worksheet_Change not working? See my last post.
Dealing with changing cell values
Textbox ControlSource property problems...

If you need help adapting this, post away and/or email your workbook...

 

[bfloyd360]

were do put this code at? One that sheet from which the cells data changes?

 

[Legacy 98055]

Replace your current line:

Sheets(2).Range("A65536").End(xlUp).Offset(1, 0) = Time

with the code I posted. This was, the number format will only apply to the range above...

 

[bfloyd360]

Ok Thanks alot.....

 

[bfloyd360]

Still Issues

That corrected the issue with making all the cells in the time format.
But what I have now is when one stop station is false (AA10=0) and all the other stations are true (AA12 =1, AA14 =1) I get the messages and time stamp of all the stations when the workbook Calculate runs. The only thing I want to know is which station is false and when it goes true again. When one goes false I don't want to log that the other stations are still true. I'm using DSData Server as my DDE software by AutomationDirect.com. They have trial software you can download and it will work for 30 mintues at a time. I have the full unlocked version. Everything about it works great other then me trying to log all this information down.

 

[Legacy 98055]

You are still using the Calculate event? Did you note "Don't use the calculate event. It's usually a very poor workaround when working with DDE data" from my first reply to you? Also, have you checked out the relevant links that I posted? If so, did you consider the SetLinkOnData Method? Are there actual DDE link in your cells? There is an easy solution to your problem if you will provide some more details.

 

[bfloyd360]

Link

Sorry, I'm new at this VB stuff, so alot of this cloudy. As for having DDE links in my cells yes. Here are some examples that I'm using.

' The code below turns on the internal bit C500 in the plc. In the cell location (16,27) I have entered the Value of 1 (binary for on). When I click the button, the plc reads the data value stored in 16, 27 and then writes the information to the internal bit C500

Sub WriteDataBtn_Click()
Channel = DDEInitiate("DSDATA","GKCounter")
DDEPoke Channel, "C500", Cells(16,27)
DDETerminate Channel

In the actual cell I use the following to link the data from Excel to DDE/OPC to the plc.

I did look at you links and will study them more today while it's calm around here. Yesterday was a mad house. I want to thank you for taking the time to help me through this. I was trying to find more on SetLinkOnData Method?? Can you show me an example of this and how it might work...
Thanks again.
Bill

This line reads the state of the input X0 and returns a binary value of 1 or 0 to the cell location.
Example cell location AA1
=DSDATA|GKCounter!'X0'

This line reads the value stored in a double word memory address and returns the value in a double word BCD format.
=DSDATA|GKCounter!'V2000:DB'

The following VB code will read a plc location and store it to the area I say.

Sub WriteDataBtn_Click()
Channel = DDEInitiate("DSDATA", "GKCounter")
Cells(1,2) = DDERequest ("V2001", Channel)
DDETerminate Channel

What I'm doing in the sheet is I have select a row of cells and entered the links example ("=DSDATA|GKCounter!'X0'") in each cell. I then want a macro to watch these cells depending on when they change perform the log time and text message.

 

[bfloyd360]

Would This Work??

I found this on the web, not sure if it would work or not. I know you said to stay away from the worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set Vrange =Range("InputRange")
For Each cell In Target
If Union(cell, Vrange).Address = Vrange.Address Then
Msgbox "The changed cell is in the input range."
End if
Next cell
End Sub

where InputRange is the range of cells monitored for changes. Each cell in the InputRange defines a DDE link to another application which pushes the data by using the DDE link syntax =application|topic!item

 

[bfloyd360]

Compile Error

I'm getting a Complie Error that reads:
End If without Block If

I'm getting it with this code-
Private Sub Worksheet_Calculate()
var1 = [AA10]

If var1 = 0 Then
With Sheets(2).Range("A65536").End(xlUp).Offset(1, 0)
.Value = Time
.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) = "USDA Station 1 Line 1 Stopped The Line"

End With
End Sub