When adding a contols to a sheet at run time using the following

oleControl = Controls.AddControl(control, this.Range[cell, missing], name);

Note the second parameter of Range is missing

I get different behaviour depenging on whether the control is a user control or not. If control is NOT a user control eg a buton, then the button fills the cell spilling over into adjacent cells.

However if the control is a userControl it is restricted to within the confines of the address cell, so only a smal portion of the control can be seen without having to resize the cells.

I would like my user Control to be displayed in the same way it should spill overe onto adjacent cells, displaying its full size as created in the designer. I want to be able to specify the second parameter of Range a missing, otherwise some knowledge as to how the big the control is and the size of the cells is required in order to calculate what the cell should be. Similarly I dont want to specify the position in terms of pixels, since this isnt user friendly, and essentially, I am aksing the user where they want to specify the control.

Any help on getting my user control to behave in the same way as a WinForms control would be appreciated.

Thanks for the response

I got a bit further with this.
(BTW this is all being done in worksheet startup)

What I have noticed is that calling Controls.Add(...)
actaully resizes the user control to fit the dimensions of the range
being supplied, in this case its doing it to the 1 cell, as I pass missing as the 2nd
range parameter

In order to try and maintain the size I now do the following

//must get the size before we add the control
double height = control.Height;
gouble Width = control.Width;

Microsoft.Office.Tools.Excel.OLEObject oleControl; // missed this line in original post
oleControl = Controls.AddControl(control, this.Range[cell, missing], name);

//now resize the OLEObject
oleControl.Height = height;
OleControl.Width = width;

This is mostly working. The values when breaking at this point are 330 (H) and 256 (W) .
However after initialisation of the worksheet is complete, my user control
looks quite a bit bigger than it should.
Breaking again, I see these values are 440 and 341.

What is going on here? What is causing my control to be resized ??

Any help much appreciated.

To answer the previous questions, the user control has a number of controls
on it (dateTimePicker's, ComboBox's, TextBox's and Label's )

Thanks for the response - AutoSize is set to false. I cant find an AutoScale property.

Its a bit wierd - the "growth" amount is a bit odd, its not like its fitting it to the the range of cells.

I have tried also with a very simple user control with just 1 control on and it behaves similarly. Its very frustrating - I have this very nicely laid out control that looks great, but then I put it on the spreadsheet and I get all this extra dead space to the right and to the bottom.

With my simple user control the size is 170,170 but once displayed is 226,226.

The growth rate is about consistent with my more complext control eg 170 /226 = .752, 330/440 = 0.75 and 256/341 = 0.75.

Still at a loss.

• why not just place your control on a modal dialog and position it near the location on the spreadsheet where you want/need it
• grab the location/size of the cell where you would like the control to appear, set your control to that location/size, display it (without a border), but not actually added to the excel controls collection; in other words, a modal window that "hovers" over the cell type of thing ...
• Not sure this is possible, but: what about setting the dimensions of the cell into which you're adding your control, before you add it ...
  

Hi,

Yes, when you add a control dynamically using a Range for position, we will try to size that control to the size of the range. However, we can only do that within the limits of what the control will allow. So, you should be able to get the behavior you want by setting the MinimumSize and MaximumSize properties on the UserControl itself. This will cause the control to "snap back" to its expected size in response to our attempts to shoehorn it into the range. This works for me--albeit I'm working with the next version of the product.

Sincerely,

Geoff Darst

Microsoft VSTO Team

That worked. Thankyou, that was really bugging me.