Excel VBA: Move Userform Based on Active Cell?

Hi All,

I'm looking for a way to emulate the behaviour of Excel's 'Find' Dialog.
Specifically, I want my userform to move out of the way so that the
selection is always visible.

(i.e: If the selection changes and becomes hidden behind the userform, the
userform needs to move out of the way so that the selection is visible
again).

I've tried playing with it by repositioning my userform based on the the
selection.top and selection.height properties, but selection.top is defined
as being 'The distance from the top edge of row 1 to the top edge of the
range'. What I need is the distance from the top of the application's window
to the top of the selection, but I don't seem to be able to find anything...

The selection will always be an entire row, so I'm only worried about
top/height properties, not width.

some pseudo-code:
(this assumes that the top property behaves as I'd like it to, not as it
actually does)

sub PopulateForm()

'do things...

dim NewTop

if (selection.top > userform.top) and (selection.top < (userform.top +
userform.height)) then 'selection is obscured
if (selection.top + userform.height)>window.height then
'Cannot fit form below selection...
newtop = selection.top - userform.height
'put form above selection
else
newtop = selection.top + selection.height
'put form below selection
end if
end if

userform.top = newtop

end sub

this gives really odd results, because a row might right at the top of the
window, but many rows down the spreadsheet, causing the form to be way
further down than required.

Any Ideas?

Surely there's some way of determining where the selection is located in
relation to the window?

Thanks,
-Dale Maggee

Dale,

This should give you the selection top.
However, if the selection has been scrolled out of the visible range,
you may not get the number you need.
(there's always the RefEdit control, if you can manage to tame it)
'-----------------
Sub TestTopRow()
Dim lngVR As Long
Dim lngSel As Long
lngVR = ActiveWindow.VisibleRange.Top
lngSel = Selection.Top - lngVR
MsgBox lngSel
End Sub
'----------------------------

Regards,
Jim Cone
San Francisco, USA


"Dale Maggee" <[email protected]> wrote in message
news:[email protected]...
Hi All,

I'm looking for a way to emulate the behaviour of Excel's 'Find' Dialog.
Specifically, I want my userform to move out of the way so that the
selection is always visible.

(i.e: If the selection changes and becomes hidden behind the userform, the
userform needs to move out of the way so that the selection is visible
again).

I've tried playing with it by repositioning my userform based on the the
selection.top and selection.height properties, but selection.top is defined
as being 'The distance from the top edge of row 1 to the top edge of the
range'. What I need is the distance from the top of the application's window
to the top of the selection, but I don't seem to be able to find anything...

The selection will always be an entire row, so I'm only worried about
top/height properties, not width.

some pseudo-code:
(this assumes that the top property behaves as I'd like it to, not as it
actually does)

sub PopulateForm()

'do things...

dim NewTop

if (selection.top > userform.top) and (selection.top < (userform.top +
userform.height)) then 'selection is obscured
if (selection.top + userform.height)>window.height then
'Cannot fit form below selection...
newtop = selection.top - userform.height
'put form above selection
else
newtop = selection.top + selection.height
'put form below selection
end if
end if

userform.top = newtop

end sub

this gives really odd results, because a row might right at the top of the
window, but many rows down the spreadsheet, causing the form to be way
further down than required.

Any Ideas?

Surely there's some way of determining where the selection is located in
relation to the window?

Thanks,
-Dale Maggee

Jim,

Wow, Thanks for the quick response!

Eureka! I think that will do nicely, and I even have a way around the
limitation you mentioned which will suit my purposes: I'll just do a
selection.show before getting the visiblerange.top value...

Cheers,

-Dale

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> Dale,
>
> This should give you the selection top.
> However, if the selection has been scrolled out of the visible range,
> you may not get the number you need.
> (there's always the RefEdit control, if you can manage to tame it)
> '-----------------
> Sub TestTopRow()
> Dim lngVR As Long
> Dim lngSel As Long
> lngVR = ActiveWindow.VisibleRange.Top
> lngSel = Selection.Top - lngVR
> MsgBox lngSel
> End Sub
> '----------------------------
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> "Dale Maggee" <[email protected]> wrote in message
> news:[email protected]...
> Hi All,
>
> I'm looking for a way to emulate the behaviour of Excel's 'Find' Dialog.
> Specifically, I want my userform to move out of the way so that the
> selection is always visible.
>
> (i.e: If the selection changes and becomes hidden behind the userform, the
> userform needs to move out of the way so that the selection is visible
> again).
>
> I've tried playing with it by repositioning my userform based on the the
> selection.top and selection.height properties, but selection.top is
defined
> as being 'The distance from the top edge of row 1 to the top edge of the
> range'. What I need is the distance from the top of the application's
window
> to the top of the selection, but I don't seem to be able to find
anything...
>
> The selection will always be an entire row, so I'm only worried about
> top/height properties, not width.
>
> some pseudo-code:
> (this assumes that the top property behaves as I'd like it to, not as it
> actually does)
>
> sub PopulateForm()
>
> 'do things...
>
> dim NewTop
>
> if (selection.top > userform.top) and (selection.top < (userform.top +
> userform.height)) then 'selection is obscured
> if (selection.top + userform.height)>window.height then
> 'Cannot fit form below selection...
> newtop = selection.top - userform.height
> 'put form above selection
> else
> newtop = selection.top + selection.height
> 'put form below selection
> end if
> end if
>
> userform.top = newtop
>
> end sub
>
> this gives really odd results, because a row might right at the top of the
> window, but many rows down the spreadsheet, causing the form to be way
> further down than required.
>
> Any Ideas?
>
> Surely there's some way of determining where the selection is located in
> relation to the window?
>
> Thanks,
> -Dale Maggee
>
>