ActiveX controls only work in Single window

[ExcelDeveloper]

Hi,

I have a workbook where I have created multiple windows using the
ActiveWindow.NewWindow & Application.Windows.Arrange methods.

My problem is that the workbooks has several embedded
ActiveXControls(listboxes,commandbuttons,etc.). If you click on the control
in any of the windows other than the first window, the control doesn't
respond.

To use the control you must go to the first window and select the control
from there. At this point the control appears on the other windows with black
lines running diagonnally across it.

Is there any way to get around this behavior without having to move the
worksheet controls off the sheet and into a command bar or menu?

thanks,

Ben

 

[NickHK]

Whilst this refers to Office97, I still see the same in Excel 2002.
http://support.microsoft.com/kb/168392
"For an ActiveX control to appear and function properly in multiple windows
or panes of the same document, the container application must support the
IViewObject interface. Because Word, Microsoft Excel, and PowerPoint do not
support this interface (as discussed in the previous section), the behavior
of an ActiveX control that is viewed in two different windows or panes of
the same document may be unpredictable."

Seems like you're out of luck.

NickHK

 

[PapaDos]

Maybe you can use the controls from the "Forms" toolbar instead of the
"Control ToolBox". They don't have that limitation (at least command buttons
and listboxes work ok)...

 

[ExcelDeveloper]

That is a possible alternative, but you don't seem to be able to get at the
properties of the controls like you can with the controls in the control
toolbox. For example how can I name the control or use events like on focus
and click to fill a listbox. It seems like I can only assign a macro to the
controls.

 

[PapaDos]

The "Forms" objects are not activeX objects, they don't have events or
dedicated VBA code or "properties" dialog. Depending on what you are doing,
they may not be powerful enough.
You can manipulate them a bit in VBA, they are considered "Shapes" of the
worksheet where they appear.

For example
debug.print Sheet1.shapes(1).name
will print the name of the first shape of Sheet1 in the immediate window.
You can use the name of the shape as an index to "shape" also.

Without knowing how you currently use your controls, I can't tell you if the
"Forms" controls are good enough for the job, but they are adequate in many
situations...

 

[ExcelDeveloper]

I often uses list and comboboxes which I populated programatically with events

 

[PapaDos]

Well, it's your choice, you either live with the present behaviour or you try
to find a different set of controls that works...