Positioning a userform to a cell

[fraz627]

I would like to position a userform to a selected cell position (top, bottom, right or left). Ideally it would work regardless of the amount of scroll and or column width or row height.

Thanks

 

[Micron]

I found some posts elsewhere that had code for a goal that was similar in nature (i.e. things like selected ranges were being used) that I thought I could adapt. After spending an hour or more on this, I'm convinced that the .Top and .Left properties of cells are in different units than what the userform is using. Saying this because when I get the top and left of E17 and the form opens, and I check the form top and left properties, they are virtually the same numbers. However, the form is just left of E and above row 11. So far I have not been able to find what the units of measure are for top and left properties for cells and forms.

One trick I saw was to insert a short line at the cell border then use the shape (or was it OLEobject) properties to locate the coordinates, then use those, then delete the line. Seems like a hack to me and I'm not going there. I just thought I'd make you aware of the idea.

So if you can figure out what the units of measure are for cell top/left vs userforms then perhaps a fudge factor can be determined. OTOH, far simpler to open the form and move it with the mouse, given how complicated this seems to be.

 

[Micron]

This may get you started. If it positions the form close to where you need it for E17 (just happens to be what I needed to use) then you can see if it works for other columns. I suggest you test on a new sheet and change the code to reflect the name of that sheet. Then try variations of row/column (and edit the code each time) to see if the fudge factor becomes too far off (say between row 5 and 55 or 555 or whatever). The column position doesn't seem to matter as much given that the code moves over right 1 column to get that column's left property (cells don't seem to have a .right property). The factor seems to be based on one UOM being pixels and the other being DPI. You can see that is a factor of 72 as I have in the code. The reason I added 20 to the left value is that my form seems to have a reserved area on the left, so the left form border is not aligned with the column edge, but just left of it. It's as if some "thing" on the form is the deciding factor of where the left side is, and that's not the form border. If it was on the opposite side, I'd say it was an area reserved for scroll bars (have seen this before in Access), but it's on the left so I don't know what that could be.

Private Sub userform_initialize()
Dim ws As Worksheet

With Me
    .StartUpPosition = 0
    .Top = Sheets("Sheet1").Range("E17").Top / 0.72 '+ Sheets("Sheet1").Range("E17").Height ' + .Height
    .Left = Sheets("Sheet1").Range("E17").Offset(0, 1).Left + 20
End With

End Sub

If you decide to pursue this and the above is OK, then your next step might be to pass the active cell rather than a hard coded address. Since userforms don't seem to have an OpenArgs property (why??) then I think the solution could be to pass the cell top/left values to module level variables, then have the userform initialize event get the values from there. Not enough info revealed to say much about that at this point. It can depend on what event you're using to launch the userform. If it's a cell double click, right click, change or selection event then those all require somewhat different coding.

 

[Akuini]

Please, check out this discussion:

Position UserForm Relative to Cell

Hi everyone, I downloaded the sample workbook FormPositioner from Pearson's website. First I replaced each occurence of VBA6 with VBA7 in the code. Then I set the horizontal and vertical positions as follows... HO = cstFhpFormLeftCellRight ' set these to how you want the form positioned...

www.mrexcel.com

 

[fraz627]

Thanks I can work with that, however it doesn't take into account for scrolling, or screen views i.e. view headings, formula bar, ect.