Modeless Form in Excel

[SkipVought]

I am have brain-loss!

I have a form that I am initially positioning on a sheet, relative to the ActiveCell.

If I scroll down on my sheet and select a cell, my form is now positioned off the active window somewhere.

It seems that my form position is relative to the window and not the sheet.

How can I properly position my form?

My code

With ActiveSheet.UsedRange
    With Target
        If .Row / 2 = Int(.Row / 2) Then
            lRow = .Row
        Else
            lRow = .Row + 1
        End If
    End With
    With ufmMaterialParms
        .Show
        .Top = Cells(lRow, 1).Top + Cells(lRow, 1).Height + 85
        .Left = Cells(Target.Row, 2).Left + 20
    End With
End With

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[steveouthwaite]

Hi Skip

As you have discovered, the cells.top and .left are relative to the excel window, not the screen.

you will need to add in Application.top / .left (the screen coordinates of the excel window), and also the .top and .left for the worksheet (if it's not maximised)


These will give you the screen co-ords


SteveO

 

[steveouthwaite]

... and references to the relevant pane in case the worksheet is in split view...

...there are porobably others...



There must be a way to return the screen coordinates easier than this.

SteveO

 

[SkipVought]

Application.Top may do it. I am not at work today, so I'll try it on...

Monday, Monday, so good to me.
Monday, Monday...

oh well, this is every other day...
;-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

steve,

I ended up using ActiveWindow.VisibleRange

    With Target
        If .Row > 3 Then
            UserForm1.Top = .Top + .Height - ActiveWindow.VisibleRange.Top + 121
            UserForm1.Left = .Left + .Width - ActiveWindow.VisibleRange.Left + 20
            UserForm1.Show
        Else
            UserForm1.Hide
        End If
    End With

I am positoning the form to the left of and below the selected cell. I don't know why I need the "fudge factors" of 121 & 20, do you?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi Skip,

The ActiveWindow.VisibleRange and your Userform have different origins.

ActiveWindow.VisibleRange.Top is relative to the top of Row 1 in the Worksheet (allowing for any hidden rows). This is the same basis as the ActiveCell.Top. Left works in the same way.

Userforms are Windows Windows (if you see what I mean) and their co-ordinates are measured from the physical top (and left) of the screen.

The 'fudges' you have relate to the height of the Title Bar and whatever Toolbars you have, and the Row and Column headers in your worksheet. If your Excel window is not maximized you are going to have even more problems.

You need to factor in [blue]Application.Top[/blue] as Steve said, and also the ActivePane (as well as or instead of the ActiveWindow - I haven't tried mocking this up, so I'm not entirely sure).

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.