not your average activate event
 

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?

--
lcorey
-----------------------------------------------------------------------
lcoreyl's Profile: http://www.excelforum.com/member.php...nfo&userid=204
View this thread: http://www.excelforum.com/showthread.php?threadid=55751

not your average activate event
 

anyone have ideas??


--
lcoreyl
------------------------------------------------------------------------
lcoreyl's Profile: http://www.excelforum.com/member.php...fo&userid=2042
View this thread: http://www.excelforum.com/showthread...hreadid=557514

not your average activate event
 

Yes, I have an idea.

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


lcoreyl wrote:
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 only
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 VB
closes book1??


--
lcoreyl
------------------------------------------------------------------------
lcoreyl's Profile: http://www.excelforum.com/member.php...fo&userid=2042
View this thread: http://www.excelforum.com/showthread...hreadid=557514

not your average activate event
 

lexcel Wrote:
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.

lexcel Wrote:
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..

--
lcorey
-----------------------------------------------------------------------
lcoreyl's Profile: http://www.excelforum.com/member.php...nfo&userid=204
View this thread: http://www.excelforum.com/showthread.php?threadid=55751

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

not your average activate event
 

That works out. Thanks

--
lcorey
-----------------------------------------------------------------------
lcoreyl's Profile: http://www.excelforum.com/member.php...nfo&userid=204
View this thread: http://www.excelforum.com/showthread.php?threadid=55751