Problem displaying a listbox over a combobox

[Josh Sale]

This is a pretty weird one and a little hard to describe ... but I'm going
to try.

My add-in sometimes places a combobox onto the user's worksheet. In
response to a click of an image control also on the worksheet, the code may
dynamically pop-up a listbox on the worksheet. Depending on many factors,
the listbox may get popped up in a location where it overlaps the combobox.

As long as no entry in the combobox is selected (i.e., no entry in the
combobox is highlighted) then everything works fine. However, if one of the
entries in the combobox is selected (i.e., the entry is highlighted) when
the user clicks the image control and the listbox happens to overlap this
combobox, then the rendering of the listbox is messed up. Its almost as if
the combobox entry is bleeding through. If the user first clicks a cell of
the worksheet (thus unselecting the contents of the combobox) before
clicking the image control then everything works fine.

This problem occurs on Excel 97 - 2003.

I've tried programmatically setting the zorder when popping up the listbox
(lstBox.ShapeRange(1).ZOrder msoBringToFront) and that didn't help.

I've tried programmatically selecting a cell on the worksheet to undo the
selection in the combobox (Selection.Select or Selection.Activate) and that
didn't help.

I select and active the listbox (lstBox.Select, lstBox.Activate) and that
doesn't help.

SetFocus doesn't seem to be supported for listbox controls on worksheets.

Argh!

Any suggestions?

TIA,

josh

 

[Jim Cone]

Josh,

Just a couple of ideas (untested) you could try out...

1. Set the visible property of the combobox to false when you show the listbox.
2. Show the listbox in a column that will not overlap the combobox
and use ScrollColumn property of the sheet to position it so the
list box will be seen.

Regards,
Jim Cone
San Francisco, CA

 

[Josh Sale]

Thanks Jim. Setting the visible property of the combobox did the trick!