Trying to build code to automatically, triggered by plc data.

[brmcbride]

I have a spreadsheet set up in testing right now that has a macro to send the current sheet in an email. The macro works perfectly. What I am trying to figure out is how to automatically run this macro. I have a dde connection setup to a plc. The code I'm attempting to use is-

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case "$A$2"

Call Mail_ActiveSheet

Case Else

End Select

End Sub

In Cell A1 I have =RSLINX|Print!'F8:0' (its a 0 or 1). Initially I was trying to watch this data in cell A1 to run the macro. I'm a beginner with VBA... It will not work to watch A1, I'm assuming because =RSLINX|Print!'F8:0' isn't actually changing. My last attempt was to copy cell A1 with =VALUE(A1) to A2 and have the worksheet_change watch cell A2. This is where I'm stuck. All of the code executes if I manually change the value in A2 by typing it. Any suggestion would be greatly appreciated.

 

[HalfAce]

Hi,
This may be good news or bad news, I'll let you decide.
What you're doing should work fine, (considering your code executes manually the way you want), but you'll want to use the Worksheet_Calculate event for that instead of the Worksheet_Change. You're right in your perception that cell A1 is not actually changing (even if the plc output does). The formula there isn't changing, only the result is.
The downside to using the ws_calculate event is that it executes every time the sheet calculates anything, and while you can test for the desired output in A1 before sending the email, if you have any other formulas on that sheet, the code will run every time any of them have to calculate which may not be desireable. (if you have a lot.)

Does that help?

 

[brmcbride]

This does help. I have been scouring every forum I could find about this and my results are the same. This does make it a doable project, depending on how many sheets one could have in a project? I was hoping to have one trigger sheet for roughly 25 triggers to send an email to 25 different people based on which trigger enabled. My first thought is... I'll have 25 trigger sheets, and 25 invoice sheets. What is the maximum number of sheets in one project? And does sound like a good way to go about it?

 

[HalfAce]

You could likely get away with using one trigger sheet and (for example) test each email recipient's cell to determine who gets included in the mailing out.

As for how many sheets we can have in a workbook, last year I made a new timesheet project for work that made a new sheet for each day of the year. It failed before getting to 365. (I don't remember where exactly, but I think it would only handle somewhere above 300.) But, this is using xl2003. Haven't tried it at home using 2007 and I have no idea how many sheets xl2010 or '13 can do.

But either way, you should be able to have way more than 50 sheets if you want to to that route.

 

[brmcbride]

I was able to get this to work, but I found one issue that I've been trying to work out but can't seem to put my finger on it. When you open the workbook it prompts to update the dde connection to the plc. After the connection has been reestablished it goes into debug mode. If you exit out without making any changes everything will work the way I desire it to. I think the Calculate code I'm running on the sheet that triggers the macro is looking at the dde connection before it is established and gives an error code. Is there a way I can tweak this code so that it will completely ignore the result of the dde connection until it equals "1"? I guess in broken english I need to create a "not equal to 1, exit sub", but can't seem to get anything to work.

Code:
Private Sub Worksheet_Calculate()
If Cells(1, 1).Value = 1 Then
Run "Truck_1_Invoice"
End If
End Sub

 

[HalfAce]

Sorry, I haven't been here for a bit...
To create a "not equal to 1, exit sub" statement, you can do it like this:

Private Sub Worksheet_Calculate()
If Cells(1, 1).Value [COLOR=#0000cd]<>[/COLOR] 1 Then Exit Sub
Run "Truck_1_Invoice"
End Sub

Or I suppose like this too

Private Sub Worksheet_Calculate()
If Cells(1, 1).Value = 1 Then
  Run "Truck_1_Invoice"
Else
  Exit Sub
End If
End Sub

Is that what you were asking?

 

[brmcbride]

I came up with the same code as your first example. I was unfamiliar with <> being not equal. I did some much needed research that proved to be very educational. I still have the same problem unfortunately. If I open this worksheet it prompts to update the DDE topic. In the time it takes to update my trigger cell I get an "error 13, type mismatch" I can't remember the exact wording. It will go Into debug, if you just exit debug, everything is fine(now that the DDE connection has been reestablished. My goal is to find away to delay the error, and allow the connection to establish before error. Could be a nuisance to users. Any suggestions would be greatly appreciated! I'm new to VBA and every obstacle seems to be educational. Almost addicting.