none
Lose VBA control when code opens another workbook RRS feed

  • Question

  • My Excel First.xlsm workbook has code that opens Second.xlsm workbook. The code in First then renames Second, copies some ranges from First to Second, then saves and closes Second.  (First & Second are faux names for the sake of discussion)

    Here's the problem:  when the sequence is triggered by Run (F5) and Second opens, control appears to stay with it, and a code module in Second shows in the editor. Nothing more happens; none of the subsequent code in First runs.  However, when I step through the sequence, and Second opens, control does revert to First, and pressing F5 makes everything run as it should. I've encountered the same problem in other Excel/VBA applications.

    Here's the relevant code:

    Sub First.xlsm
       '
       ' Other code
       '
       Call Open_Shell_Wbk     'see next code block
       '=== Shell file is Active Wbk ====  'action stops here, but should proceed
       ' Save shell with session-specific name
       Dim RecDate As String
       RecDate = Format(Date, "mm-dd-yy")
       Dim ShellPath As String
       ShellPath = "F:\CHCM\Playback\"
       ' Session-specific name for PB file
       PlaybackWbk = "PB" & StMax & "_Stocks_" & RecDate & ".xlsm"
       ActiveWorkbook.SaveAs ShellPath & PlaybackWbk
       '===== PB file is Active Wbk =====
       '
       'Other code
       '
    End Sub
    
    Sub Open_Shell_Wbk()
       Set SessConfig = Range("Sess_Config")
       Dim ShellPath As String              
       ShellPath = SessConfig(3, 4)
       Workbooks.Open ShellPath & "Shell_PB_NewPrices.xlsm"  '"Second" file
    End Sub
    

    Why does the sequence stop when initiated by F5, but proceed when stepped through? How do I get control to automatically revert to the code in First? I did disable Workbook_Open() in Second, but that had no effect. What am I missing?

    I'm working in Excel 2013 (Office 365) on a relatively new Win 8.1 desktop PC with 12GB of RAM.

    Thanks in advance for a solution and/or advice and words of wisdom.

     

    Thursday, March 5, 2015 3:50 PM

Answers

  • Hi Rabnud,

    I could not reproduce your issue, did you get any error message? It would be helpful if you could share us a simple demo through OneDrive to help us reproduce your issue.

    >> The code in First then renames Second, copies some ranges from First to Second, then saves and closes Second.

    In my option, if your project worked correctly when step by step, but failed when Run (F5), I assume that it might be caused by UI suspend when you operate the sequence. It seems that if your second operation executed after the first operation was finished, but if y​our second operation executed when the first operation was not finished, it would run into error. I would recommend you add the DoEvents function or add if statement check whether the second method is ready. For information about the DoEvents, you could turn to the link below:

    # Definition of DoEvents in Visual Basic for Applications
    http://support.microsoft.com/kb/118468

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 9, 2015 9:44 AM

All replies

  • At a glance your code looks like it should work but without seeing the values etc hard to say, is there an error message? If not try fully declaring all your variables and place them at the top of each routine. In testing debug the values that get assigned to them and check they are as expected.

    Unless you are doing something else it looks like you are simply opening a workbook and saving with a new name. If not changing the file type maybe you could simply use the FileCopy method

    Thursday, March 5, 2015 4:54 PM
    Moderator
  • Hi Rabnud,

    I could not reproduce your issue, did you get any error message? It would be helpful if you could share us a simple demo through OneDrive to help us reproduce your issue.

    >> The code in First then renames Second, copies some ranges from First to Second, then saves and closes Second.

    In my option, if your project worked correctly when step by step, but failed when Run (F5), I assume that it might be caused by UI suspend when you operate the sequence. It seems that if your second operation executed after the first operation was finished, but if y​our second operation executed when the first operation was not finished, it would run into error. I would recommend you add the DoEvents function or add if statement check whether the second method is ready. For information about the DoEvents, you could turn to the link below:

    # Definition of DoEvents in Visual Basic for Applications
    http://support.microsoft.com/kb/118468

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 9, 2015 9:44 AM