0

For some reason Excel 2016 switches to a different workbook when I run a macro. There is no code that indicates this is happening. I've tried to switch the focus back using

Appworkbook.Activate

but it only works if I put a breakpoint there. I've tried sleep as well. It works fine on Excel 2010 and the macro does what it is supposed to do.

Has anyone experienced similar?

*****SOLUTION (Not ideal!)*****

I ended up fixing this issue but adding a new button and calling the macro from a button_click method. I have no idea wy that worked and assigning the macro to the button didn't.

RShome
  • 489
  • 2
  • 11
  • 35
  • 1
    How about `ThisWorkbook.Activate` at the end of the code? – Vityata Nov 08 '17 at 11:23
  • 1
    Are you opening the other workbook with the macro? By default that one will become the `ActiveWorkbook`. Apart from that this is one of the many reasons to [avoid activation and selection in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Rik Sportel Nov 08 '17 at 11:54
  • I didn't write the original macro but now have to fix it. Actually the macro opens up three different workbooks but seems to default to the one I don't want. In Excel 2010, it opens the first workbook and that one becomes the active workbook which is what I want. I really don't understand how it could change between Excel 2010 and Excel 2017. – RShome Nov 08 '17 at 12:20
  • 1
    Capture the workbook in a variable (`Dim wb as Workbook : Set wb = Workbooks.Open("somepath\somefile.xlsx")` and work against that object. Avoid working against `ActiveWorkbook` alltogether. – Rik Sportel Nov 08 '17 at 12:46

1 Answers1

0

I ended up fixing this issue but adding a new button and calling the macro from a button_click method. I have no idea why that worked and assigning the macro to the button didn't.

RShome
  • 489
  • 2
  • 11
  • 35