Modeless Userform to act like "Find and Replace" modeless form

[malik641]

Hey Guys & Gals,

I have a modeless form that I want to behave like the "Find and Replace" form (Ctrl+F) in the sense that when you are on the sheet and you navigate through cells the form will move 'out of your way' so to speak. I want to know if it's worth trying to create the correct coding to achieve this...or if I need any API calls...or if there's a simple trick that I don't know about :)

I have a feeling it's going to have a lot to do with the Top, Left, Height, and Width properties of the Range object and the Userform object.

Thanks in advance for any ideas/tips/links/etc :thumb

[benny]

Hello, Josef

I added a Worksheet-code.
I'm sorry i can't credit the original coder.
Hoping it suits you.

[malik641]

Hey benny,

Thanks for the reply and the example. It gives me some ideas.

I'm still striving for the "activecell/selection" dependent moving modeless form. I think I have an idea of what I'm going to.

I'll be using the following properties (although may become subject to change):
Top
Left
Height
Width

From the following objects:
Application
Userform
Selection



Now after playing with the 'Find and Replace' form, I've noticed a pattern that seems doable. When you move to a cell that would be underneath the form, the form makes a decision (or seems to) that says "okay, which is the shortest distance to move in order to not be under the Selection. And this move will be in one direction."

So now I'm trying to figure out the correct algorithm for this. :think:

[malik641]

Okay, after some further investigation I've come to realize that it's going to be very difficult to use the Top and Left properties of the Range object because of the following reasons:

1. It is measured in pixels starting at 0 at the top-left (Top, Left) corner of A1. Where the Application object is measured from the screen to the top-left corner of the main XL window...and the Window object is measured from the top edge of the window to the top edge of the usable area (below the menus, any toolbars docked at the top, and the formula bar). (Thanks help). And the UserForm object is measured from the screen.

2. If the Child window is not maximized, I cannot use a constants that hold a generic distance from the screen to the Top-Left distance of A1.


Does anyone know a way of determining the position of a cell relative to the screen? Or maybe a way of finding the position of the UserForm relative to the child window's ranges?

[johnske]

Joseph,

Application.Top gives the position of the top of the app with respect to the screen and ActiveWindow.Top gives the position of the top of the active window with respect to the 'inside' of the app while Target.Top (or ActiveCell.Top) gives the position of the top of the cell with respect to the 'inside' of the active window.

The same principle applies to the Left property, so you can specify where your userform is to be located with respect to either the screen, the active window, or the cell...

[malik641]

John,

Isn't that what I said in #4 post ("1. ...")?

Anyway, my problem lies with the fact that I want the userform to not be hovering over the Active Selection. And since the userform's Top and Left is measured relative to the screen, and the Range object (which is the most important part of this task) is measured relative inside the sheet in the Child window of XL (where (0,0) is the top left corner of cell A1), then how can I make sure the Userform will correctly move aside from the Active Selection if the position of the Range and the Userform are measured in different ways?

I need a way for both the Userform and the Range object to be measured in the same manner. To ensure without a doubt that the userform will not be over the Selection.

Here's what I came up with last night, check it out and you'll see what I mean.

You'll notice that the code is effective in the bottom half of the Userform (place the active cell in this area to see). And that's just when the child window is maximized. Try it without the child window maximized, and it throws it way off, where the "Find and Replace" form still works flawlessly.

[Charlize]

The cool listbox thing of the site of xl-logic altered by me for a userform.

Charlize

[Charlize]

Click on another cell to see the behavior. It's a selection_change event. Maybe add another portion of code to set the starting position of the form correctly (needs to be corrected).

Charlize

[malik641]

Thank you Charlize, but this behaves exactly like Benny's attachment (the code may be identical). And it's just not exactly what I'm looking for.

Press Ctrl+F to bring up the form I'm talking about and play around with that form and you'll see exactly what I'm looking for. Try moving the active cell / selection (using mouse holding left-click or Shift+Arrows) under the form and watch how it reacts.

The form does not have to move every time the active cell / Selection changes, only when the selection is beneath the form. And I don't want the form to be able to trail off the screen.

[johnske]

...Anyway, my problem lies with the fact that I want the userform to not be hovering over the Active Selection. And since the userform's Top and Left is measured relative to the screen,...This is not quite correct - I think you may've misunderstood what I said. That may be the default behaviour, but by using userform activate or initialize, the userforms top and left can be measured from where you say it will be measured from, e.g. Me.Top = Target.Top

[malik641]

I believe I understand what you mean now. Basically if I start the userform where A1's (0,0) position is, I can store the value of Top and Left of the userform into variables and use them in the comparison statements I have to determine if the cell selection is under the userform.
Not a bad idea :)

I'll give it a shot tonight if I have time :thumb thanks for the advice John.

[Tommy]

Hey Joseph,

I got this to run around the screen but it goes off the screen. I'll play some more tonight. Oh for I forget this is all in the thisworkbook module the form's name is "JosephsForm" :devil2: Very much a work in progress still.:think:

Public Xscl#, Yscl#
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ResetScl
If WithIn(Xscl + (Target.Top + Target.Height), JosephsForm.Top, JosephsForm.Height) _
And WithIn(Yscl + (Target.Left + Target.Width), JosephsForm.Left, JosephsForm.Width) Then
If (JosephsForm.Top + JosephsForm.Height) > Application.Height Then
JosephsForm.Top = Xscl + Application.Height - JosephsForm.Height
JosephsForm.Left = Yscl + Target.Left - JosephsForm.Width
ElseIf (JosephsForm.Left + JosephsForm.Width) > Application.Width Then
JosephsForm.Top = Xscl + Application.Height - JosephsForm.Height
JosephsForm.Left = Yscl + Target.Left - JosephsForm.Width
Else
JosephsForm.Top = Xscl + (Target.Top + Target.Height)
JosephsForm.Left = Yscl + (Target.Left + Target.Width)
End If
'ElseIf WithIn(Xscl + (Target.Top + Target.Height), JosephsForm.Top, JosephsForm.Height) _
'And WithIn(Yscl + (Target.Left + Target.Width), JosephsForm.Left, JosephsForm.Width) Then

End If
End Sub
Public Function WithIn(iNum#, iCompNum#, iMin#) As Boolean
If iNum >= iCompNum And iNum <= iCompNum + iMin Then WithIn = True
End Function
Sub ResetScl()
Xscl = Abs(Application.Top) + Abs(Application.Height - ActiveWindow.Height)
Yscl = Abs(Application.Left) + Abs(ActiveWindow.Left)
End Sub

[Andy Pope]

Have you seen Chip Pearson's FormPosition?
http://www.cpearson.com/excel/FormPosition.htm

Maybe you can use that to calculate the cells currently covered by the userform.

[Charlize]

This one won't go of the screen and is positioned on the leftside of the cell + the width. Still not exactly what you are looking for but work in progress. Maybe someone else has a clue. I think the form will even work when window isn't in it's maximized state. So form will still be completely visible.

Charlize

[malik641]

Thanks Charlize for the example :)


And thanks for the link Andy, I'll have a read at this :thumb I think that's what I'm looking for :yes