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.

 

[GiraffetheGeek]

Yeah I thought that might have been the case.

I appreciate the input, and anyone else's input if they think of anything

 

[mikerickson]

Instead of a pause in one routine, you could use two routines.

Public runPartTwo as Boolean

Sub Part1()
    Rem do some stuff

    runPartTwo = True
    Workbooks("otherBook.xls").Activate
End Sub

Sub PartTwo()
    Rem do more stuff
End Sub

Then in the Workbook1.xls Workbook_Activate event you would have the code.

Private Sub Workbook_Activate()
    If runPartTwo Then Call PartTwo
    runPartTwo = False
Exit Sub

Thus, the user would press the button, which calls Part1.
Stuff is done and when it gets to that point, the Public variable runPartTwo is set to True, otherBook is activated and the sub ends.
The user does stuff in otherBook and returns to Workbook1.
The Activate event triggers, running the code which sees that runPartTwo is True so the Sub PartTwo is run, finally runPartTwo is re-set to False and everything is over.

 

[GiraffetheGeek]

Thanks Mike,

I think I follow that. I may not have a chance to look at it for a little while but I will post once I have tried it.

 

[mikerickson]

The other thought that I have depends on what the user is supposed to be doing during the pause.

If the idea is "pause so the user can fill in these specific cells in the other workbook" or "pause so that the user can do one or some of this list of actions in the other workbook" it might be smoother for WB1 to have a userform that requests/inputs those values or allows the user to choose which actions to perform and does them to the other workbook.
Calling that userform could replace the pause.

If the pause is for less structured activity in the other workbook, the two part plan would be what I recommend. (Upon consideration, I think the "work only on OtherBook until Closed and returned to WB1" would be easiest mediated by Application level events rather than Workbook level events in individual workbooks.)

 

[GiraffetheGeek]

Cheers,

I had thought about the userform too, however I am still a bit of a beginner with VBA and excel.

The other complication is the first workbook will be a shared workbook.

I am working through a number of solutions but just thought there may have been a command that could have been used to tell the code to wait till a specific event.

I did some C++ programming about 10 years ago and remember there being something like that in there.

Still it's all good and I'm sure I'll be able to use some of your solutions.