none
how to find out, excel worksheet is modified or not

    Question

  • Hi,

    Scenario:

    According to client requirement entire excel toolbar is disabled, I used to control  save, move next, close operations by my own toolbar (Add-in). Through c# code I will open excel worksheet; user may or may not change that worksheet, after that they will move to next worksheet. In case of user changed the worksheet, and not saved, manually i need to popup message 'Worksheet modified, do you want to save?’ 

    I used ActiveWorkbook.Saved property to identify the changes in the current opened excel object.
    This property is working fine, except one scenario. This property is not working for the following case.

    If I select one cell eg.A10, and keyed/typed 10 and immediately (without changing the focus) I pressed my toolbar (Add-in user defined) move next option, ActiveWorkbook.Saved property is not set properly. If I key/type more than one cell ActiveWorkbook.Saved property is working properly.

    How to find out single cell changes.

    Please Help me.

    Thanks
    Govind

    Wednesday, November 05, 2008 9:32 AM

Answers

  • Hi,

     

    I have worked with my colleague on the issue. Here are our findings.

     

    The WorkSheet_Change event will not be triggered when Excel is in edit mode. I could not find a direct way to check if Excel is in edit mode or not. When Excel is in edit mode most of the toolbars and menu items will be disabled. The alternative way is to check if the toolbars or menu items are enabled or disabled.

     

    The C# code given below will check if the New menu item is enabled or disabled. If the New menu item is disabled, Excel will be in edit mode.

     

    private bool IsEditMode()

    {

       object m = Type.Missing;

       const int MENU_ITEM_TYPE = 1;

       const int NEW_MENU = 18;

     

       // Get the "New" menu item.

       CommandBarControl oNewMenu = Application.CommandBars["Worksheet Menu Bar"].FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, true );

     

       if ( oNewMenu != null )

       {

          // Check if "New" menu item is enabled or not.

          if ( !oNewMenu.Enabled )

          {

             return true;

          }

       }

       return false;

    }

     

    Please let me know if you have any questions on this.

     

     

    Thanks,

     

    Harjit

     

    Thursday, November 13, 2008 9:30 PM

All replies

  • Disclaimer

    The links in this message may lead to third-party Web sites. Microsoft provides third-party resources to help you find customer service and/or technical support resources. Information at these sites may change without notice. Microsoft is not responsible for the content at any third-party Web sites and does not guarantee the accuracy of third-party information.

     

    Hi,

     

    Thank you for contacting Microsoft Innovate On Office Advisory Services.

     

    The following is the details of your request:

     

    [Support Request Description]:

    - You would like to save an active workshop in excel after a user changes the content of a cell but does not leaves the focus from the cell.

    - The data is saved on a click of a custom developed add-in button.

     

      [Next Step]:

    Please provide us the following information

    - What version of Excel you are using with SP level.

     

    Please let me know if the following link helps.

    http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2004-02/7725.html

     

    Thanks,

     

    Harjit.

    Thursday, November 06, 2008 2:51 PM
  • Hi Harjit,

     

    Thanks for your reply

     

    I am using "Microsoft Office Excel 2007" (12.0.4518.1014) MSO (12.0.4518.1014)

    Part of Microsoft Office Enterprise 2007

     

    I checked this link also.

    http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2004-02/7725.html

     

    They referred "ThisWorkbook.Saved" property & "Worksheet_Change" event.

    In my case, while keying/typing single cell and clicking next button (Add-in), these method are not working properly

    i.e.

    "ThisWorkbook.Saved" is true, flag is not reset for single cell change and next button click event

    "Worksheet_Change" event is not called for single cell change and next button click event

     

    Eagerly waiting for your reply

     

    Thanks,

    Govind

    Monday, November 10, 2008 7:09 AM
  • Hi,

     

    Thanks for the update. I will research on the issue and get back to you as soon as possible.

     

     

    Harjit.

     

    Monday, November 10, 2008 1:00 PM
  •  

    Hi,

     

    I will am still researching on the issue and get back to you as soon as possible.

     

     

    Harjit

     

     

    Thursday, November 13, 2008 2:33 PM
  • Hi,

     

    I have worked with my colleague on the issue. Here are our findings.

     

    The WorkSheet_Change event will not be triggered when Excel is in edit mode. I could not find a direct way to check if Excel is in edit mode or not. When Excel is in edit mode most of the toolbars and menu items will be disabled. The alternative way is to check if the toolbars or menu items are enabled or disabled.

     

    The C# code given below will check if the New menu item is enabled or disabled. If the New menu item is disabled, Excel will be in edit mode.

     

    private bool IsEditMode()

    {

       object m = Type.Missing;

       const int MENU_ITEM_TYPE = 1;

       const int NEW_MENU = 18;

     

       // Get the "New" menu item.

       CommandBarControl oNewMenu = Application.CommandBars["Worksheet Menu Bar"].FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, true );

     

       if ( oNewMenu != null )

       {

          // Check if "New" menu item is enabled or not.

          if ( !oNewMenu.Enabled )

          {

             return true;

          }

       }

       return false;

    }

     

    Please let me know if you have any questions on this.

     

     

    Thanks,

     

    Harjit

     

    Thursday, November 13, 2008 9:30 PM
  • Hi Harjit,

     

    Thanking you and your colleague to spent time on this issue.

     

    I also found one solution, it's working fine but it's too complicated.

    Through menu's, is pretty simple and smart way to check activecell edit mode.

    I checked IsEditMode() method, It's working fine, I will replace this method in place of my old code.

     

    Thank you very much once again

     

    Regards,

    Govind

     

    Wednesday, November 19, 2008 6:04 AM