none
Problem disabling commands in vsto excel 2010 RRS feed

  • Question

  • Hello together,

    I'm trying to disable Excel functionality (e.g. cut/copy, save, save as) in a Excel2010 document level based application. In RibbonXML I entered the lines

    <commands>
        <command idMso="Cut" enabled="false" />
        <command idMso="Copy" enabled="false" />
    </commands>

    This works, meaning the menu button in the context menu is disabled. But the keyboard shortcut still works. I looked for corresponding events, but didn't find (the right ones).

    What's the best way to completely disable such commands?

    Any ideas are welcome.

    Monday, May 23, 2011 10:22 AM

Answers

  • Hello Calvin,

    it took me some time to try the different approaches.

    The macro approach is a problem because you need to enable macros and can't do it for a special document.

    Your second aproach only works half, because when you copy something from the sheet into the clipboard and then switch to a different program or a different instance of Excel, the deactivate event is not fired and so you can't empty the clipboard.

    After some research in the internet I found an approach hooking the keyboard events with the windows api function SetWindowsHookEx().
    Because this hooking works in the complete OS and not only in my Excel application I then only had to solve the problem to detect that the keys (CTRL+C, CTRL+S) were pressed when my vsto-document was the active window.

    Now this solution works fine for me.

    Thanks for your ideas

     

    Kay

    • Marked as answer by Kay Sommer Monday, May 30, 2011 8:29 AM
    Monday, May 30, 2011 6:46 AM

All replies

  • Kay,

    Instead of disable the built-in commands have You considered to protect the worksheet instead? In that way You can control which cell(s) or range(s) to protect which gives a better precision.

    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Monday, May 23, 2011 11:34 PM
  • This works, meaning the menu button in the context menu is disabled. But the keyboard shortcut still works. I looked for corresponding events, but didn't find (the right ones).

    What's the best way to completely disable such commands?

    Hi Kay,

     

    You can take Dennis’s suggestion to try. Protecting worksheets and workbook is a simple way to prevent worksheet from changing.

     

    Additionally, it’s unlikely to manipulate shortcut in document-level. However, here is a workaround that you can create a macro which does nothing and set the shortcut of the macro as Ctrl – v to “overload” the shortcut of paste.

     

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 24, 2011 3:33 AM
    Moderator
  • Thanks for your replies,

    I already protect the cells and the sheet. But additionally my customer doesn't want the user to copy data out of the sheet into a new one. I will think about the macro approach, but I would prefer to handle it all in my custom  c# code.

     

    Perhaps there are other possibilities?

    Tuesday, May 24, 2011 6:29 AM
  • Hi,

     

    Maybe you can try to close the clipboard when a specific worksheet is activated, and open the clipboard when some other worksheets with this workbook are activated or this workbook deactivated.

    CloseClipboard Function

    OpenClipboard Function

    Something like:

            Excel.Worksheet sheet = null;

            private void ThisWorkbook_Startup(object sender, System.EventArgs e)

            {

                sheet = this.Worksheets["Sheet1"];

                this.SheetActivate += new Excel.WorkbookEvents_SheetActivateEventHandler(ThisWorkbook_SheetActivate);

                this.Deactivate += new Excel.WorkbookEvents_DeactivateEventHandler(ThisWorkbook_Deactivate);

            }

     

            void ThisWorkbook_Deactivate()

            {

                //open clipboard

         

            }

     

            void ThisWorkbook_SheetActivate(object Sh)

            {

                   Excel.Worksheet sh = Sh as Excel.Worksheet;

                   if (sheet == sh)

                   {

                       //close clipboard

                   }

                   else

                   {

                       //open clipload

                   }

            }

     

    I’m not sure this will do the trick. Meanwhile, if you want to protect the data from copying, why don’t you just protect the worksheet from selecting?


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 25, 2011 12:17 PM
    Moderator
  • Hi Kay,

    I'm look forward to hearing of you. Any update?


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 30, 2011 2:08 AM
    Moderator
  • Hello Calvin,

    it took me some time to try the different approaches.

    The macro approach is a problem because you need to enable macros and can't do it for a special document.

    Your second aproach only works half, because when you copy something from the sheet into the clipboard and then switch to a different program or a different instance of Excel, the deactivate event is not fired and so you can't empty the clipboard.

    After some research in the internet I found an approach hooking the keyboard events with the windows api function SetWindowsHookEx().
    Because this hooking works in the complete OS and not only in my Excel application I then only had to solve the problem to detect that the keys (CTRL+C, CTRL+S) were pressed when my vsto-document was the active window.

    Now this solution works fine for me.

    Thanks for your ideas

     

    Kay

    • Marked as answer by Kay Sommer Monday, May 30, 2011 8:29 AM
    Monday, May 30, 2011 6:46 AM
  • Hi Kay,

    Thanks for your update.

    I'm glad to see that you solved the problem and thanks for sharing your solution. In additionally, would you like to mark your reply as answer to let other members see the solution more clearly :-)

     

     


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 30, 2011 7:43 AM
    Moderator