Excel VBA: Move Userform Based on Active Cell?

[Dale Maggee]

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 Cone]

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


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 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