not your average activate event

[lcoreyl]

I am trying to have code in book1:

sub stuff
'blah blah blah
workbooks.open (book2)
Range("A1").value = "open1"
workbooks.saveas (book2newname)
workbooks(book1).activate
workbooks(book1).close
end sub

code in book2:

private sub workbook_activate
if range("A1").value <> "open1" then exit sub
msgbox("it worked!!")
end sub

The point is that I have code that I want triggered in book2, but onl
after I open it and save it to a new name, and close book1.
This doesn't work, but if I take out the close line of code in book1
and then close book1 manually it works? Why won't this trigger when V
closes book1?

 

[lcoreyl]

anyone have ideas??

 

[lexcel]

Yes, I have an idea.

Leave out :
workbooks(book1).activate
or put after workbooks(book1).close :
worbooks(book2).activate

 

[lcoreyl]

Yes, I have an idea.
Leave out :
workbooks(book1).activate

I originally had that, but tried this as I thought that book2 might b
active all along therefore not triggering when book1 closed. I though
this would ensure that book1 was active, closes, and therefore transfer
active window to book2, but evidently that's not the case, or it is, bu
doesn't trigger the workbook_activate event.

or put after workbooks(book1).close :
worbooks(book2).activate

That doesn't work since book1 will then close, so any code after i
doens't get run..

 

[lexcel]

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

 

[lcoreyl]

That works out. Thanks