I would "pass" the variable to the subroutine via a public variable:
Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 '5 seconds
Public Const cRunWhat = "myMacroName" ' the name of the procedure to run
Public myObj As Variant 'or Range or MSForms.Combobox if you know for sure.
Sub Auto_Open()
Call StartTimer
End Sub
Sub Auto_Close()
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
'just some testing
Set myObj = ThisWorkbook.Worksheets("Sheet1").Range("A1")
Set myObj = ThisWorkbook.Worksheets("sheet1").ComboBox1
Application.OnTime EarliestTime:=RunWhen, _
Procedu="'" & ThisWorkbook.Name & "'!" & cRunWhat, _
Schedule:=True
End Sub
Sub myMacroName()
If TypeName(myObj) = "Range" Then
MsgBox myObj.Address(external:=True)
ElseIf TypeOf myObj.Object Is MSForms.ComboBox Then
MsgBox "It's a combobox"
End If
'get ready for the next time
StartTimer
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=False
End Sub
FYI: You may want to take a look at Chip Pearson's notes:
http://www.cpearson.com/excel/OnTime.aspx
Greg Lovern wrote:
I understand how to pass number and string arguments with OnTime. But
what about object arguments? Can I pass a range object, combobox
object, etc.?
Here's how I'm passing number and string arguments. How do I pass
object arguments?
Function tester1()
Const NUM_ARG As Long = 5
Const STRING_ARG As String = "String"
Debug.Print "tester1: " & NUM_ARG & ", " & STRING_ARG & ", " & Now
Application.OnTime Now + 0.0000001, "'tester2 " & NUM_ARG & ", """
& STRING_ARG & """'"
End Function
Function tester2(NUM_ARG, STRING_ARG)
Debug.Print "tester2: " & NUM_ARG & ", " & STRING_ARG & ", " & Now
Debug.Print
End Function
Thanks,
Greg
--
Dave Peterson