how to position a listbox???
 

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

how to position a listbox???
 

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.

"Brad K." wrote:

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

how to position a listbox???
 

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

"K Dales" wrote:

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.

"Brad K." wrote:

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

how to position a listbox???
 

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

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

--
Regards,
Tom Ogilvy

"Brad K." wrote in message
...
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

"K Dales" wrote:

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.

"Brad K." wrote:

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

how to position a listbox???
 

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

"Tom Ogilvy" wrote:

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

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

--
Regards,
Tom Ogilvy

"Brad K." wrote in message
...
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

"K Dales" wrote:

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.

"Brad K." wrote:

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