none
Disable Excel UI until a method has finished (HRESULT: 0x800AC472) RRS feed

  • Question

  • I have an issue where if you click rapidly on a sheet that's being written to by a method of mine it throws the error given in the title

    I've looked at a number of forum threads who've all had this issue during the method they're trying to run

    Mine is different - I don't want anything to interrupt it, and so none of the solutions seem appropriate

    I have tried putting in a call to protect the sheet but that doesn't prevent the error being thrown, it simply pops up an extra dialogue

    Please can someone advise how I can effectively disable mouse actions on this workbook or sheet while the routine is running?

    NB - this code does NOT fix the problem, just throws the extra dialogue mentioned above

        this.Protect(true, true, true, true, true,
            false, false, false, false, false, false, false, true,
            true, true);
        this.EnableSelection = Excel.XlEnableSelection.xlNoSelection;


    Wednesday, January 3, 2018 4:10 PM

All replies

  • Hello Journeyman-UK,

    >>I have an issue where if you click rapidly on a sheet that's being written to by a method of mine it throws the error given in the title

    I'm wondering how would you accomplish this. I tried to create an VSTO add-in and created a method to write data to active worksheet. In my test, clicking rapidly on the sheet does not get the error.

    Here is the code I'm using.

         for (int i = 1; i <= 1000; i++) {
                    for (int j = 1; j <= 100; j++) {
                        Globals.ThisAddIn.Application.ActiveSheet.Cells[i, j].Value = i + j;
                    }
                }
                MessageBox.Show("Finished");

    In the case, operation on Excel UI is forbidden. Could you please show us your code and repro steps so we could try to reproduce your issue?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 4, 2018 8:30 AM
  • Hello Journeyman-UK,

    >>I have an issue where if you click rapidly on a sheet that's being written to by a method of mine it throws the error given in the title

    I'm wondering how would you accomplish this. I tried to create an VSTO add-in and created a method to write data to active worksheet. In my test, clicking rapidly on the sheet does not get the error.

    Here is the code I'm using.

         for (int i = 1; i <= 1000; i++) {
                    for (int j = 1; j <= 100; j++) {
                        Globals.ThisAddIn.Application.ActiveSheet.Cells[i, j].Value = i + j;
                    }
                }
                MessageBox.Show("Finished");

    In the case, operation on Excel UI is forbidden. Could you please show us your code and repro steps so we could try to reproduce your issue?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thanks for the reply

    I was able to make a simple loop work without incident, but what appears to be happening is when the loop gets more complicated, it's possible to interrupt

    Here is a very small sample from my code that causes the method to throw the exception if interrupted by user clicking on a cell before its complete

    The code does NOT appear to error (it happens so fast, it might be a timing issue) if the output of each part of the foreach statement isn't being converted into the relevant string value

    var fam_result = await send.PostToAzure(sendToAzure, "Shift/GetFamily");

    List<RotaData> wrk = new List<RotaData>();

    wrk = JsonConvert.DeserializeObject<List<RotaData>>(fam_result);

    int rowIndex = 2;
    int cellIndex = 2;

    string shift;
    string start;
    string end;
    string hols;
    string day;
    string worker_name;


                    foreach (RotaData shift_list in wrk)

                    {

                        System.Diagnostics.Debug.WriteLine("Debug: going through an iteration of the loop");

                        shift = shift_list.SHIFT_NO;
                        start = shift_list.START_TIME;
                        end = shift_list.END_TIME;
                        hols = shift_list.HOLIDAY_OR_TERM;
                        day = shift_list.DAY_OF_WEEK;
                        worker_name = shift_list.WORKER_NAME;

                        sheet.Cells[rowIndex, cellIndex] = "output";

                        rowIndex++;

                    }


    Thursday, January 4, 2018 4:49 PM
  • I still don't have a proper fix but have created a workaround which at least handles the error better

    I've put in a SheetChangeEvent which will display a message pop up telling the user the operation has been aborted and to run it again

    Unforutnately that's the best I can do, I can't get it to carry on when the original exception is thrown

    Friday, January 5, 2018 1:33 PM
  • Hello Journeyman-UK,

    As far as I know, Excel Object model does not provide the method to prevent user operation on Excel UI.

    As workaround, you could try to hide the worksheet before code running and un-hide it at last in case of clicking rapidly on the sheet.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 8, 2018 2:09 AM