none
Excel 2013 VBA 7.1 Workbook Activate Problems RRS feed

  • Question

  • I have 2 workbooks open and during the course of a macro am switching between them. The macro has developed a problem where it seems to disregard the workbook.activate command when running. If I set a breakpoint at the line with the activate command and step through it, it works fine.

    I have tried activating using ThisWorkbook,Worksheets().activate, Workbook(xx).activate, Windows(xx).activate. I have also tried minimizing the other window which I do not want activated, but this causes the main macro to freeze.

    This is only a problem at one point in my macro and it works properly elsewhere.

    Monday, February 2, 2015 3:29 PM

Answers

  • I have 2 workbooks open and during the course of a macro am switching between them. The macro has developed a problem where it seems to disregard the workbook.activate command when running. If I set a breakpoint at the line with the activate command and step through it, it works fine.

    I have tried activating using ThisWorkbook,Worksheets().activate, Workbook(xx).activate, Windows(xx).activate. I have also tried minimizing the other window which I do not want activated, but this causes the main macro to freeze.

    This is only a problem at one point in my macro and it works properly elsewhere.

    There is no need to "switch" between the workbooks, just assign a reference to them, and access the methods and properties for each workbook.
    Dim wb1 as Workbook, wb2 as Workbook
    'assuming both workbooks are already open
    Set wb1 = Workbooks("Name of First WB")
    Set wb2 = Workbooks("Name of Second WB")
    Also unless you really need to observe what the macro is doing during execution, there is no reason to have multiple Workbook.Activate statement. Just have one final one at the end of the macro to activate the workbook you really want to see at the end of the macro execution.
    Monday, February 2, 2015 3:59 PM
  • John,

    Thanks so much for the reply.

    I had been using the references in the macro, but you're right about not having multiple activate statements- I was over-complicating the code.. The problem I was having was running a loop on one of the workbooks, and trying to make sure that workbook was activated properly so that the loop did not run in the wrong workbook. The macro seems to be running properly now.

    Ken

    Monday, February 2, 2015 7:20 PM

All replies

  • I have 2 workbooks open and during the course of a macro am switching between them. The macro has developed a problem where it seems to disregard the workbook.activate command when running. If I set a breakpoint at the line with the activate command and step through it, it works fine.

    I have tried activating using ThisWorkbook,Worksheets().activate, Workbook(xx).activate, Windows(xx).activate. I have also tried minimizing the other window which I do not want activated, but this causes the main macro to freeze.

    This is only a problem at one point in my macro and it works properly elsewhere.

    There is no need to "switch" between the workbooks, just assign a reference to them, and access the methods and properties for each workbook.
    Dim wb1 as Workbook, wb2 as Workbook
    'assuming both workbooks are already open
    Set wb1 = Workbooks("Name of First WB")
    Set wb2 = Workbooks("Name of Second WB")
    Also unless you really need to observe what the macro is doing during execution, there is no reason to have multiple Workbook.Activate statement. Just have one final one at the end of the macro to activate the workbook you really want to see at the end of the macro execution.
    Monday, February 2, 2015 3:59 PM
  • John,

    Thanks so much for the reply.

    I had been using the references in the macro, but you're right about not having multiple activate statements- I was over-complicating the code.. The problem I was having was running a loop on one of the workbooks, and trying to make sure that workbook was activated properly so that the loop did not run in the wrong workbook. The macro seems to be running properly now.

    Ken

    Monday, February 2, 2015 7:20 PM
  • Hi Ken,

    >>I have tried activating using ThisWorkbook,Worksheets().activate, Workbook(xx).activate, Windows(xx).activate. I have also tried minimizing the other window which I do not want activated, but this causes the main macro to freeze.<<

    I am failed to reproduce the issue. Could you rerpoduce the issue in two new workbooks?

    If yes, I suggest that you try to disable all add-ins to see whether the issue was fixed.

    If not, is there any special code for the specific workbook? I suggest that you comment the code to narrow down this issue.

    Regards & Fei


    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.

    Tuesday, February 3, 2015 2:57 AM
    Moderator