Triggering an event based on actions in another Workbook

[Jay-T]

Hi,

I'm developing a counter/timer spreadsheet.

Initially the spreadsheet uses an rtd which pulls information from elsewhere to detect if the counter on/off conditions are met in combination with an application.ontime sub, however the refresh rate is too slow. The triggering event can be less than a second, so the code I've written can't detect the triggering action and sometimes, the rtd doesn't refresh in time to detect.

One possible way to detect this trigger is based on an Active X button on another spreadsheet. Pressing once starts/stops an action.

I can't modify the triggering spreadsheet as it's a secured controlled document, so if I could write a script on another excel spreadsheet to detect either the triggering of sub/clicking of the button that would be great.

How would I start off on this?

Thanks!

Joel.

[xlnitwit]

Hi Joel,

Can you provide more detail as to what the trigger actually is? Absent that information all I might suggest is:
- use Windows timers rather than OnTime
- use Withevents variables for either the button or workbook, depending on what event you actually need to catch.
- Use Setlinkondata to run your code based on the RTD calculating.

[Jay-T]

Hi,

Thanks for the reply.

The spreadsheet has fair bit more complication to it but the crux of My code is as:

Sub Check()

With Workbooks("Timer").Worksheets(1)

' If it detects that the second counter has passed the 1 min mark then it backs the Time In counter up to the time it went out
If .Range("Test2") = "OUT" And DateDiff("s", .Range("OutTime"), Time()) >= 59 Then
.Range("OutCheck") = True 'If set to true, it means that the counter logs as out.
.Range("SecondCheck") = False
End If

If .Range("Test") = "OUT" And .Range("Test2") = "IN" Then
.Range("SecondCheck") = True 'Gets the second counter to start Running
.Range("OutTime") = Time() ' logs the time it went out for comparison later if relevant
.Range("Test2") = .Range("Test") ' logs the current status as out
GoTo Whoop
End If

If .Range("Test") = "IN" And .Range("Test2") = "OUT" Then
.Range("SecondCheck") = False 'Stops the second out counter
.Range("Test2") = Range("Test")
If .Range("OutCheck") = True Then
.Range("DurationOut") = .Range("TotalOutTime") 'Adds the time out to the total time out counter
.Range("OutCheck") = False ' changes status to in
End If
End If

Whoop:
TTime = DateAdd("s", 1, Now())
Application.OnTime TTime, "Check"
.Range("Running") = "Timer Running"

End With

End Sub

So as mentioned I'm trying to log when a status change occurs. I have a cell is in the spreadsheet that gets a time based on the NOW() function. Application .ontime is only for checking when the status change occurs.

Range("Test") is triggered by an RTD. This RTD's update frequency unfortunately may not be fast enough and this I cannot change. However information fed into the RTD which causes the change is actually from another spreadsheet and based on a couple of ActiveX buttons. This being the "source" trigger, would make it the only way to detect the status change. But the "source" cannot be touched hence I need a way of tracking the trigger from outside the source workbook.

Thanks!

[xlnitwit]

From what you say, it may be easier to use the SetLinkOnData method, but here is an example of how to hook into the command buttons.

1. Add a new class module to your workbook and add the following code to it
   

   Please Login or Register  to view this content.

2. In a new module in your workbook, you need code like this
   

   Please Login or Register  to view this content.

3. You will need to adjust the names of the class, workbook, worksheet and commandbutton to match your circumstance.

After running checkit once, clicking the button in the other workbook should pop up a message box.

[Jay-T]

Thanks! Works like a charm, Never thought of using a class module.

FYI anyone else who wants to use this needs to be aware to add the Forms 2.0 reference, was a bit stumped when it wasn't working.