Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default Macro that waits, then resumes after window is closed

Does anyone know how to do the following:

I have a macro that does some things
including open up a workbook, then I need the macro to wait until this
workbook is closed while I edit some values, then proceed with the rest
of the macro.


Any ideas?

Thank you all in advance.

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default Macro that waits, then resumes after window is closed

You will need 2 macros, one that opens the workbook and stops, and then trap
the BeforeClose event of that workbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Does anyone know how to do the following:

I have a macro that does some things
including open up a workbook, then I need the macro to wait until this
workbook is closed while I edit some values, then proceed with the rest
of the macro.


Any ideas?

Thank you all in advance.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 176
Default Macro that waits, then resumes after window is closed

How do you 'stop' in VBA?
If you Exit Sub, then how do you get going again?
If you find better than this, please let me know:

Sub Main() ' (XL97/WIN98)
Dim WBName$, i%
Workbooks.Add
WBName = ActiveWorkbook.Name
MsgBox "Just opened " & Workbooks(WBName).Name
Do While XLWinStat(WBName) ' this is the 'wait' loop
' the for..next is to not hog the system
For i = 1 To 1000: DoEvents: Next i
Loop
MsgBox WBName & " is closed. Am proceeding ..."
End Sub

Function XLWinStat%(WBName$)
On Error GoTo XLWSerr
If Workbooks(WBName).Name = WBName Then
XLWinStat = 1: Exit Function
End If
Stop ' never gets here
XLWSerr: ' gets here when WBName is closed
End Function

Bob wrote:
You will need 2 macros, one that opens the workbook and stops, and then trap
the BeforeClose event of that workbook.


michael wrote
Does anyone know how to do the following:
I have a macro that does some things
including open up a workbook, then I need the macro to wait until this
workbook is closed while I edit some values, then proceed with the rest
of the macro.


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default Macro that waits, then resumes after window is closed

ignore please
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
closed workbook macro puiuluipui Excel Discussion (Misc queries) 1 July 4th 08 10:13 PM
how to get alert message when window is closed in excel for blank Anitha Excel Discussion (Misc queries) 0 November 30th 06 07:08 AM
Project still in "project explorer" window after file closed down! Gunnar Johansson Excel Programming 16 December 22nd 04 07:21 PM
Project still in "project explorer" window after file closed down! Gunnar Johansson[_3_] Excel Programming 0 December 7th 04 02:43 PM
Run a Macro over a Closed Workbook?? John[_78_] Excel Programming 8 February 25th 04 03:28 PM


All times are GMT +1. The time now is 06:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"