none
Excel 2013 switches back to the original window after opening a new workbook. RRS feed

  • Question

  • While trying to update an Excel vsto to work with Excel 2013 from Excel 2010 I have run into an issue I cannot resolve:

    The process run by the vsto is:

    (At the start Book1 is the active workbook)

    1. User clicks on a ribbon button which displays a form (having to use showdialog to keep it visible (another issue!))

    2. While the form is open a new workbook (book2)  is added and data is added to the new workbook.

    3. The form closes when the operation is complete.

    In Excel 2010 the new workbook (book2) is displayed at the end of the process but when the vsto runs in Excel 2013 the original workbook (book1) is displayed and the user has to manually switch to the new book2.

    Adding code to switch to book2 at the end of the process does not work as it appears that switching back to book1 happens once all my code has finished running.

    This is obviously as a result of Excel 2013 having an SDI but I can't find any advice on how to get around this issue.

    Any advice greatly appreciated.

    Wednesday, December 11, 2013 1:00 PM

Answers

  • Thanks for your response.

    The issue turned out to be because I had used .ShowDialog rather than .Show  to display the form.

    If I change to using .show method to display the form the activeworkbook is displayed at the end of the sub that adds the new workbook

    I  had switched to .showdialog this because the form would disappear while processing, which defeated the purpose of a using it to display a progress bar.

    I think I have solved that issue using this link:

    http://www.jkp-ads.com/Articles/keepuserformontop.asp

    Your response helped me realise my mistake so thanks again.

    • Proposed as answer by Marvin_Guo Thursday, December 12, 2013 5:42 AM
    • Marked as answer by Marvin_Guo Tuesday, December 17, 2013 1:51 PM
    Wednesday, December 11, 2013 6:23 PM

All replies

  • Hello MerlinXL,

    Here are the key points you need to correct in your add-in:

    1. You need to call the Show method of your form for displaying it on top of the Excel window. Just pass an instance of the IWin32Window interface to the Show method:

    class ArbitraryWindow : IWin32Window 
    {
        public ArbitraryWindow(IntPtr handle) 
        { 
            Handle = handle; 
        }
        public IntPtr Handle { get; private set; }
    }
    
    newForm.Show(new ArbitraryWindow(new IntPtr(Application.Hwnd)));

    2. You can use the ScreenUpdating property of the Application class from the Excel Object Model for turning off or on screen updating while doing something in the background (in the code of your add-in).

    3. The System.Windows.Forms.Form class provides the Closing and Closed events. One of them (the latter, for example) you can handle for activating the just added sheet. Don't forget to turn the ScreenUpdating property on.

    Let me know whether all these helps or not.  

    Wednesday, December 11, 2013 4:02 PM
  • Thanks for your response.

    The issue turned out to be because I had used .ShowDialog rather than .Show  to display the form.

    If I change to using .show method to display the form the activeworkbook is displayed at the end of the sub that adds the new workbook

    I  had switched to .showdialog this because the form would disappear while processing, which defeated the purpose of a using it to display a progress bar.

    I think I have solved that issue using this link:

    http://www.jkp-ads.com/Articles/keepuserformontop.asp

    Your response helped me realise my mistake so thanks again.

    • Proposed as answer by Marvin_Guo Thursday, December 12, 2013 5:42 AM
    • Marked as answer by Marvin_Guo Tuesday, December 17, 2013 1:51 PM
    Wednesday, December 11, 2013 6:23 PM