View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
lexcel lexcel is offline
external usenet poster
 
Posts: 34
Default not your average activate event

Ok, I admit I underestimated the problem. Hopefully I didn't insult
your intelligence by that.
I did not realise that the code was running in the workbook to be
closed, so as you pointed out, there is no use putting code after the
..Close.
To close book1 from a subroutine in book2 also didn't work as I (and
probably you too) discovered. The close method seems to perform an
implicit "End", even if the caller is not in the workbook being closed.

The workaround I found is as follows:
'_________________________________________________ ______

Sub stuff()
'blah blah blah

Workbooks.Open (book2)
Range("A1").Value = "open1"
ActiveWorkbook.SaveAs (book2newname)
Application.OnTime Now + TimeValue("00:00:01"), _
"'" & ActiveWorkbook.Name & "'!Continue"
Workbooks(ThisWorkbook.Name).Close
End Sub

'_________________________________________________ ______
' in Book 2 :
'_________________________________________________ ______

Sub Continue()
MsgBox "Entered Sub Continue in " & ThisWorkbook.Name

' more stuff & blah blah

End Sub
'_________________________________________________ ______

I hope this code does what you need/want.

Good luck,

Lex