Excel screen updating problem

[ameetpatel]

Hi

This is a first post so I appreciate your patience!

I am building an Excel COM addin in Visual C#. I have some methods which call other methods etc, each of which is doing something 'visible' within Excel such as hiding/showing rows, or copy/pasting values.

At the start of the root method and each method which is called, I have entered the line:

excelapp.Application.Screenupdating = false;

and set it back to true at the end of the root method.

But the screen still seems to be updating (e.g. user can see rows being hidden in step and the cutcopymode dotted border shows briefly)? Is it the case that in C# screenupdating is reset within called sub-methods? Or does anyone have any other ideas?

Thanks!
Ameet

[foamy]

Welcome to the forums

That depends, how are your methods using the Excel Interop? Could you post some code to clarify? (remember to use [code] tags)

If the user is not meant to see the Excel sheet being edited, I would set the Visible property of the Excel application to false until the work has completed - but I'm not sure it would work in your case.

[ameetpatel]

Happy to post the code... but it's long and probably a bit ugly to experienced C# programmers. How about I first try to clarify further what I'm aiming to do and if that's no help then I will post the code?

- the excel file is basically a financial model
- as part of the add-in, I have a task pane
- in that task pane, I have a treeview box with each node/child-node corresponding to specific line(s) in the model
- when the user expands/collapses the tree nodes, the relevant lines are hidden/shown and in some cases other operations are performed (e.g. formulae in certain lines are changed or existing entries are copied and pasted in as values)

So, with regards the structure of the code:
- the task pane class contains the node expand/collapse methods
- these call methods from another class which contain all methods governing the behaviour of the model (and in fact some of those call other methods in other classes)

I don't want to hide the application or workbook while running this code since I think that would appear strange from a user perspective - ideally, I just want the screen to stop updating when a user clicks on expand/collapse and then show the result (i.e. screenupdating false >> true).

Does that help explain the query?

thanks again!

[foamy]

I've just created this tiny test-app, which seems to work.
Although I've worked with Excel Interop before, I've never used the ScreenUpdating property.

It seems to work using my sample code:

            Excel._Application excelApp = new Excel.Application();
            object missing = Type.Missing;
            excelApp.ScreenUpdating = false;
            excelApp.Visible = true;
            Excel.Workbook book = excelApp.Workbooks.Add(missing);
            Excel.Worksheet sheet = book.Worksheets.Add(missing, missing, missing, missing);
            sheet.Cells[1, 1] = "Test";
            System.Threading.Thread.Sleep(2000);
            excelApp.ScreenUpdating = true;

[ameetpatel]

thanks. Yep, I can get that to work. The problem arises when another method is called from within that one sometime after the ScreenUpdating = False line... So for example, instead of

sheet.Cells[1,1] = "test"

something like:

MyMethod(Excel.range rng, string text)

Does that make sense?

[ameetpatel]

ps, note that within 'MyMethod' in the example above, I also have excelapp.ScreenUpdating = false there

[foamy]

Can you replicate it using a test-project like the one I posted?

I tried adding a button to my test-form, which sets the ScreenUpdating property. It also seems to work just fine:

public partial class Form1 : Form
{
    private Excel._Application excelApp;
    private Excel.Workbook book;
    private Excel.Worksheet sheet;

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        excelApp = new Excel.Application();
        object missing = Type.Missing;
        excelApp.ScreenUpdating = false;
        excelApp.Visible = true;
        book = excelApp.Workbooks.Add(missing);
        sheet = book.Worksheets.Add(missing, missing, missing, missing);
        sheet.Cells[1, 1] = "Test";
        System.Threading.Thread.Sleep(2000);
        excelApp.ScreenUpdating = true;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        excelApp.ScreenUpdating = false;
        sheet.Cells[1, 2] = "Test";
        System.Threading.Thread.Sleep(4000);
        excelApp.ScreenUpdating = true;
    }
}

[ameetpatel]

You're right - when I break it down into a simple test project, it seems to work ok. Will have to look into why it's not working in my app...

Thanks for your help. For your reference, the type of scenario in which I though it was not working was:

public partial class Form1 : Form
    {
        private Excel._Application excelApp;
        private Excel.Workbook book;
        private Excel.Worksheet sheet;

        
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            excelApp = new Excel.Application();
            excelApp.Visible = true;

            book = excelApp.Workbooks.Add();
            sheet = book.Worksheets.Add();
        }


        private void button2_Click(object sender, EventArgs e)
        {
            excelApp.ScreenUpdating = false;
            object missing = Type.Missing;

            for (int i = 1; i < 50; i++)
            {
                for (int j = 1; j < 20; j++)
                {
                    sheet.Cells[i, j] = "test"; 
                }
            }

            OtherClass.SubMethod(excelApp, sheet);

            excelApp.ScreenUpdating = true;
        }

    }

where there is another class as follows:

class OtherClass
    {

        public static void SubMethod(Excel._Application excelApp, Excel.Worksheet wks)
        {
            excelApp.ScreenUpdating = false;

            for (int i = 1; i < 50; i++)
            {
                for (int j = 1; j < 20; j++)
                {
                    wks.Cells[i, j] = "replace";
                }
            }

            excelApp.ScreenUpdating = true;

        }


    }

[krishnap2w2]

I have encountered a similar problem with Screen Update. I resolved it. I attachment has the solution with screenshots.

How to Avoid Screen Updating in Excel.compressed (1).pdf

[hoangkiena222014]

upppppppppppppppppppppp phụ chủ thớt

[krishnap2w2]

I have encountered a similar problem with Screen Update. I resolved it. I attachment has the solution with screenshots.

How to Avoid Screen Updating in Excel.compressed (1).pdf

[krishnap2w2]

I have encountered a similar problem with Screen Update. I resolved it. I attachment has the solution with screenshots.

How to Avoid Screen Updating in Excel.compressed (1).pdf

[krishnap2w2]

I have encountered a similar problem with Screen Update. I resolved it. I will explain how to handle the screen updates using macro. We can speed up our macro by turning off the screen updating while the macro runs, by adding the below line of code to the macro:

Sub Macro () Application.ScreenUpdating = False
Write the necessary code Here
——————-
——————- Application.ScreenUpdating = True End Sub.

The prior versions of excel 2000, it was not required to turn Screen Updating back to true. When the macro is finished, Excel would revert back to turning the screen updating to true.
But now whether you are writing macros in Excel 97, Excel 2000, Excel 2002, 2003, 2007 or 2010 always we have to turn the screen updating back to true with the below code:
Application.ScreenUpdating = True

How to Avoid Screen Updating in Excel.compressed (1).pdf