Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Move Userform Based on Active Cell?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Move Userform Based on Active Cell?
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move from active cell offset in macro | Excel Discussion (Misc queries) | |||
Move active cell to same location on each worksheet | Excel Discussion (Misc queries) | |||
Why doesn't active cell move using PageDown key in Excel 2007? | Excel Discussion (Misc queries) | |||
need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
How can I move the active cell in Excel with the arrow keys? | Excel Discussion (Misc queries) |