Module vs Sheet VBA Operation

Question

I must not be understanding how event proceedures and subs operate when they are in a Excel Module vs code within a Sheet.  The code below is in a sheet and with a couple command buttons in the sheet.  When I select Start_Stream I receive a message that the sub Update marco can not run and is either not available to the workbook or Macros are disabled.  The later is not the case.  I find that if I move the Update sub to a module it will run however when I select Stop_Stream I receive a message that the Object Method 'OnTime' application failed. 

I have used this same code to start/stop a sub routine in other files (in modules) but this one does not operated properly.  Im thinking it has something to do with trying to run from the sheet.  I'm very puzzled and any help would be appreciated or point me to some tourial that might help me better understand the conflict that my code is causing.

thanks

dave

Private Sub Start_Stream_Click()

Application.OnTime Now, "UpDate"

End Sub

Private Sub Stop_Stream_Click()

Application.OnTime mdnexttime, "UpDate", , False

End Sub

Public Sub UpDate()

….code…..

mdnexttime = Now + TimeValue("00:00:05")

Application.OnTime mdnexttime, "Update"

End Sub

Answer 1

The procedure Update has to be in a standard module in order to be called from OnTime.

You don't show the definition of mdnexttime, but it would need to be accessible to both the procedures that use it.

I would suggest replacing the activeX buttons with Forms buttons (ActiveX controls on a worksheet are likely to be troublesome), put all the code in a standard module and assign the macros to the buttons.

Dim mdNextTime As Date

Sub Start_Stream_Click()

  UpDate

End Sub

Sub Stop_Stream_Click()

If mdnexttime<>0 Then Application.OnTime mdnexttime, "UpDate", , False

mdnexttime = 0

End Sub

Sub UpDate()

….code…..

mdnexttime = Now + TimeValue("00:00:05")

Application.OnTime mdnexttime, "Update"

End Sub

Answer 2

Thank you bill.  Your advice cleared things up.