how to position a listbox???

[Guest]

I have a macro that displays a ListBox when certain cells are clicked on. I
want the ListBox to be just on the right side of the selected cell with the
top of the box at the cell if the cell is in the top half of the screen and
the bottom of the box if it is in the bottom half.
Excel 2003, XP
Thanks,
Brad

 

[Guest]

Seems simple, but not so easy. The listbox is a variation of the Shape
object, which has a top, left, height and width that can be set. The problem
is determining what the settings should be. First of all, how do you know
when the selected cell is in the top or bottom half of the screen? That
itself is tricky. Depends on the cell address, the current scroll position
of the worksheet, as well as the height of all the columns and width of the
rows. You can use the Application.Window object to try to figure all this
out. Then calculate your offset (horizontal, vertical) in points to set the
top and left of your listbox. I'm afraid I don't have time to work this all
out, but at least this gives a path to a possible way to do this.

 

[Guest]

I had this working before I upgraded to Excel 2003. Now I can't figure out
where the cell is in the window. ActiveCell.Top used to work but now it
seems to give a value relative to the top of the spreadsheet rather than the
top of the screen. Any advice?
Thanks,
Brad K

 

[Tom Ogilvy]

? range("K31").Top - ActiveWindow.VisibleRange(1).top
140.25

should give you the top of K31, relative to visible range.

 

[Guest]

Thank you Tom. This was exactly what I was looking for and everything is
working again.
Brad K