Hi Tushar,

Yes I should have mentioned it's not possible to pass an object OnTime,
especially as the OP had named the variable 'Wb'. I suppose as an
alternative to setting a global object reference it would be possible to
pass the workbook's string name and then set a new reference in the called
macro. I wasn't sure what the OP was doing.

I also use OnTime in the open event of some addins to do things that are
only possible once there is an activeworkbook, eg certain application level
settings. Like you I use 'Now' as it will only be called after everything
else has loaded, including any workbooks primed to load.

Regards,
Peter T


"Tushar Mehta" wrote in message
m...
You cannot pass an object as an argument with the method John (Green)
demonstrated. How to use a variable is something Peter has already shown
you.

You can always use a global variable to pass an object to a OnTime
procedure
-- provided you can guarantee that that object variable will remain
untouched, and in your case I believe that will indeed be true.

So, in a standard module:

public aWB as workbook

sub CreateNewWorksheet ()
'aWB is now accessible from here
end sub

In the class module where you have the application variable:

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
dTimeDefault = Now() + TimeValue("00:00:05")
set aWB=Wb
Application.OnTime dTimeDefault, "CreateNewWorksheet"
End Sub

Also, if you are using this technique to defer execution of some code
because of problems doing so in the WorkbookOpen procedure, you should be
able to use just Now() rather than impose a 5 second delay. It's a
"trick"
I, myself, have used on more than a few occasions. Essentially,

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
set aWB=Wb
Application.OnTime Now(), "CreateNewWorksheet"
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi



I have sub below, which calls another proc after it finishes (it seemed
to
error otherwise).



Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)

..

..



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

Application.OnTime dTimeDefault, "CreateNewWorksheet"

.

.



End Sub





I now want to add a parameter to the CreateNewWorksheet procedure call
(and
the proc itself) i.e. something like:



Application.OnTime dTimeDefault, "CreateNewWorksheet(" & Wb & ")"



But it errors!



Please can you help with the syntax?



THANKS



Tim



PS Have searched on Google and John Green kindly helped previously
(2002)
but it was for a fixed parameter rather than a variable (anyway I am
stuck
now!):


http://groups.google.com/group/micro...ing/browse_thr

ead/thread/c5a0ba87b7bc254f/b9bb97043d13dd44?lnk=st&q=Application.Ontime+Par
ameter&rnum=3&hl=en#b9bb97043d13dd44