Can a pause be placed in code?

[GiraffetheGeek]

Hi there,

I have a button in a spreadsheet.

What I want is the when the button is pressed it's code is run to a certain point which will open another spreadsheet. I want it to then pause and wait until the other spreadsheet is closed before restaring and completing.

Is this possible?

This is what I have so far

Private Sub Btn_Budgetors_Click()
    Workbooks.Open Filename:= "file.xls"
    MsgBox ("Remember to update Service Plan")
    ActiveWorkbook.Save
End Sub

So I want it to run the first line, and then wait until the other spreadsheet is closed before running the last two lines.

Cheers in advance for your help.

 

[phagan]

with some code from other places it would look something like this:

''''''''''''''''''''''''''''''''''''''''''
'Written by www.ozgrid.com
'Test to see if a Workbook is open.
''''''''''''''''''''''''''''''''''''''''''
Dim wBook As Workbook
    On Error Resume Next
Recheck:
    Set wBook = Workbooks("file.xls")
        If wBook Is Nothing Then 'Not open
            
            Set wBook = Nothing
            On Error GoTo 0
        else
            Application.Wait Time + TimeSerial(0, 0, 5)
            goto Recheck
        End If

 

[GiraffetheGeek]

Thanks - but excuse my ignorance but how would I integrate that into the code I posted?

 

[mikerickson]

What would the user be doing during this pause? Entering into the worksheet or sipping coffee (or some other non-Excel activity)?

 

[GiraffetheGeek]

They would be entering some information into the other spreadsheet and then saving and exiting.

 

[phagan]

oops

 

[phagan]

Well with the little bit of code you have there this should do the trick.
I have not fully tested it, it is a bit of code that I use in one of my own spreadsheets, modified for your use.

Private Sub Btn_Budgetors_Click()     
     Workbooks.Open Filename:= "file.xls"
'''''''''''''''''''''''''''''''''''''''''' 
'Written by www.ozgrid.com 
'Test to see if a Workbook is open. 
'slightly modified to do other stuff
'''''''''''''''''''''''''''''''''''''''''' 
Dim wBook As Workbook     
On Error Resume Next 
Recheck:     
     Set wBook = Workbooks("file.xls")         
If wBook Is Nothing Then 'Not open
             Set wBook = Nothing
             On Error GoTo 0        
else
             Application.Wait Time + TimeSerial(0, 0, 5)
             goto Recheck         
End If
'''''''''''''''''''''''''''''''''''''''
'End of the Ozgrid and my own code
'''''''''''''''''''''''''''''''''''''''
MsgBox ("Remember to update Service Plan")     
ActiveWorkbook.Save 
End Sub

 

[phagan]

OK I finally had some time to test that and it didn't work the way I expected.

I'm sorry. First bad advice I have given in a long time. I'll see if I can work something up for you.

 

[GiraffetheGeek]

Thats cool, I hadn't had a chance to test it yet, cheers for the help.

 

[phagan]

The only way I could figure out how to do what you want was to use a custom userform, set it modal = false with an OK button and a message about not clicking until after changing the workbook.

Private Sub Btn_Budgetors_Click()
    Workbooks.Open Filename:= "file.xls"
    CustomUserform.show 
 '' MsgBox ("Remember to update Service Plan")
    ActiveWorkbook.Save 
End Sub