satpp

Hi,

I have an Excel customization where I call Controls.AddButton() in the Startup of a few sheets. This was causing the active sheet to change as each Startup was called which looked awkward so I wrapped each of the Startups with a ScreenUpdating=false blocks and an Application.Goto() call to the ActiveCell before AddButton().

Focus now stayed on the active sheet that the user had opened the workbook with but the controls on that sheet were no longer painted initially and thus looked as if they were missing. Moving the mouse cursor over where the buttons were would redraw and and cause them appear.

I added an if (Application.ActiveSheet != this.InnerObject) check before turning off ScreenUpdating and it seems to do what I want but is there a better way to keep the focus in same spot after an AddButton() call or alternatively a method to force redrawing like Invalidate()

Thanks.


Re: Visual Studio Tools for Office Adding to Excel Controls collection changes focus

Ji Zhou – MSFT

Hi,

The following codes work fine in my side:

Code Snippet

this.Application.ScreenUpdating = false;

Globals.Sheet1.Controls.AddButton((Excel.Range)Globals.Sheet1.Cells[1, 1], "sheet1Control");

Globals.Sheet2.Controls.AddButton((Excel.Range)Globals.Sheet2.Cells[1, 1], "sheet1Control");

Globals.Sheet3.Controls.AddButton((Excel.Range)Globals.Sheet3.Cells[1, 1], "sheet1Control");

Globals.Sheet1.Activate();

this.Application.ScreenUpdating = true;

You just need to activate a sheet not a cell. And then change the ScreenUpdating back to true.

Thanks

Ji






Re: Visual Studio Tools for Office Adding to Excel Controls collection changes focus

satpp

That code snippet doesn't seem to work for me. It had the same problem as my code that activated a cell where the active sheet doesn't draw any of the controls to the screen.

I tried it on a new project with only your code snippet in the Sheet1_Startup() and the button on Sheet1 still wasn't rendered until I covered the containing cell with another window, switched sheets, or moused over it which forced it to redraw. The same problem occurs when I ran it on a different computer.

Thanks.




Re: Visual Studio Tools for Office Adding to Excel Controls collection changes focus

Ji Zhou – MSFT

Hi Satpp,

I think the difference is that, we used the different versions of Office and VSTO. The above codes I gave work fine in Excel 2007 document developed by VSTO3.

I have found a test machine, and prove that the problem only exists in Excel 2003 and VSTO.

But, in fact, there are many workarounds.

1. The way you mentioned above, using Application.ActiveSheet != this.InnerObject.

2. Add controls in Sheet1, after setting ScreenUpdating back to true in my given codes:

Code Snippet

this.Application.ScreenUpdating = false;

Globals.Sheet2.Controls.AddButton((Excel.Range)Globals.Sheet2.Cells[1, 1], "sheet1Control");

Globals.Sheet3.Controls.AddButton((Excel.Range)Globals.Sheet3.Cells[1, 1], "sheet1Control");

Globals.Sheet1.Activate();

this.Application.ScreenUpdating = true;

Globals.Sheet1.Controls.AddButton((Excel.Range)Globals.Sheet1.Cells[1, 1], "sheet1Control");

3. Get the control’s handle in Sheet1, and call its Refresh() method:

Code Snippet

this.Application.ScreenUpdating = false;

Button btn = Globals.Sheet1.Controls.AddButton((Excel.Range)Globals.Sheet1.Cells[1, 1], "sheet1Control");

Globals.Sheet2.Controls.AddButton((Excel.Range)Globals.Sheet2.Cells[1, 1], "sheet1Control");

Globals.Sheet3.Controls.AddButton((Excel.Range)Globals.Sheet3.Cells[1, 1], "sheet1Control");

Globals.Sheet1.Activate();

this.Application.ScreenUpdating = true;

btn.Refresh();

4. Add controls in other sheets only when the host sheet is first time activated:

Code Snippet

bool flagS2 = true, flagS3 = true;

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

{

Globals.Sheet1.Controls.AddButton((Excel.Range)Globals.Sheet1.Cells[1, 1], "sheet1Control");

Globals.Sheet2.ActivateEvent += new Microsoft.Office.Interop.Excel.DocEvents_ActivateEventHandler(Sheet2_ActivateEvent);

Globals.Sheet3.ActivateEvent += new Microsoft.Office.Interop.Excel.DocEvents_ActivateEventHandler(Sheet3_ActivateEvent);

}

void Sheet3_ActivateEvent()

{

if (flagS3)

{

Globals.Sheet3.Controls.AddButton((Excel.Range)Globals.Sheet3.Cells[1, 1], "sheet1Control");

flagS3 = false;

}

}

void Sheet2_ActivateEvent()

{

if (flagS2)

{

Globals.Sheet2.Controls.AddButton((Excel.Range)Globals.Sheet2.Cells[1, 1], "sheet1Control");

flagS2 = false;

}

}

You can choose whatever you would like to adopt. Hope this helps!

Thanks

Ji






Re: Visual Studio Tools for Office Adding to Excel Controls collection changes focus

satpp

Thanks! I'm using the Refresh() workaround now since it draws faster than workaround 1.

A side question: If Refresh() is called on a control not on the active sheet (ie Sheet3), does call just return with minimal performance hit or does the method do other stuff besides drawing I'm just wondering if my (Application.ActiveSheet == this.InnerObject) checks before calling Refresh() were unneccessary.




Re: Visual Studio Tools for Office Adding to Excel Controls collection changes focus

Ji Zhou – MSFT

satpp wrote:
If Refresh() is called on a control not on the active sheet (ie Sheet3), does call just return with minimal performance hit or does the method do other stuff besides drawing

Hi,

Refresh() only forces the control to invalidate its client area and immediately redraw itself and any child controls. It does not do other stuff.

Thanks

Ji