Move a single combo box from one cell to another...

[Jimm]

This has probablly been answered before but does anyone have any sample code
show how I would go about moving the locaiton of a combo box dependent on
where the user clicks. I know it's probablly going to involve the
SelectionChange event of the worksheet but I can't seem to get the combo to
display in the new location.
Sample code to follow::::

if (cbo1 != null)
{
Globals.Sheet1.Controls.Remove(cbo1);
cbo1 = null;
}
selection = Target ;
cbo1 =
(Microsoft.Office.Tools.Excel.Controls.ComboBox)Globals.Sheet1.OLEObjects(name);
cbo1.Tag = Globals.Sheet1.Controls.AddControl(cbo1, selection,
name);
cbo1.Name = name;
cbo1.Activate();


Thanks in advance..
Jimm

 

[Norman Jones]

Hi Jimm,

See Debra Dagleish's code and detailed
instructions at:


http://www.contextures.com/xlDataVal10.html

The above ComboBox is intended for
Data Validation but should answer all
of the problems that you raise.

 

[Jimm]

Thanks - I've looked at Debra's code and gleaned what I can to do it in C#
but the problem I seem to be running into is that I'm unable to create a
combo box at runtime in the "SelectionChange" event.

This code run in the Sheet "StartUp" event works and create a combo and
displays it...

selection = Globals.ThisWorkbook.Application.get_Range("H5",
"H5");
cbo1 = new Microsoft.Office.Tools.Excel.Controls.ComboBox();
cbo1.Tag = Globals.Sheet1.Controls.AddControl(cbo1, selection,
name);
cbo1.Name = name;

the identical code in the "SelectionChange" event does not work. Any thoughts?

Jimm

 

[Norman Jones]

Hi Jimm,

The indicated code does not create a
ComboBox in the SelectionChange event:
the code shows or hides a previously
created ComboBox.

Therefore, it should suffice to create the
ComboBox programmatically and then
use techniques similar to those employed
by Debra to hide / unhide the ComboBox
in the SelectionChange event.