How to position form in same place with different screen resolutions

J

Joe 90

Hi

Have a little form I use as a popup to take user back to main worksheet. My
dev env is running at 1024x768, but users run at 800x600.

the top and left properties work from top and left, so only ever give a
relative position from top and left. So if the resolution goes down, the
form vanishes off to the right!

I don't want the popup to obscure the data/charts I am using, how can I make
it always go to a top right position, or even better inside the excel window
(below the toolbars and to the left of the vertical scroll bar).

Regards

Joe
 
J

Joe 90

Hi Bob

I'll have a play with this, but Chip's solution only seems to apply to
positioning forms with cells. If my form, which is modeless, is open infront
of a chart, I'll have to work on the position option for that too? I guess
its all there, just have to work at it!! Thanks for your help.

Joe

Joe,

This might be of help http://www.cpearson.com/excel/FormPosition.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

Jon Peltier

Joe -

To go to the top left of the screen, use cells(scrollrow, scrollcolumn)
as your base. To work around charts, use the chart object's top left
cell property. Chip's technique is very powerful and flexible; it is
intended to provide the top and left positions, but you can use just the
top, for example, and provide a left position you determine any other way.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
M

Michel Pierron

Hi Joe 90;
Roughly and simply:

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
With Application
.WindowState = xlMaximized
Dim W&: W = .Width
' Zoom factor (1024 pixels = 768 points)
Me.Zoom = CInt(100 * W / 768)
Me.Top = .Height - .UsableHeight - 22
Me.Left = W - Me.Width - 19
End With
End Sub

Regards
MP
 
B

Bob Phillips

Joe,
He does talk about cells, but I believe that he gives you enough information
to work out how to achieve your objective. The important point about Chip's
article is that he covers all of the things you need to consider, and gives
you a download to play with.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

Joe 90

Along with Michel, I found the properties for UsableHeight, UsableWidth and
VisibleRange for Application and Activewindow provided all the relative
measurements in both resolutions to suitably place my little form. The
difficulty will always be the number of toolbars, status bars etc that users
have up on their application. I could get excel to fix all that but think I
have acheived what I set out to do.

Here is the code I used:

*****************************************************************
Sub setfrmloc()

Application.WindowState = xlMaximized
appuht = Application.UsableHeight
appht = Application.Height
appuwt = Application.UsableWidth
appwt = Application.Width
cbdrh = CommandBars("Drawing").Height

' ActiveWindow.DisplayHeadings = False
' insht = ActiveWindow.Height - ActiveWindow.VisibleRange.Height
' ActiveWindow.DisplayHeadings = True

With UserForm1
.Top = (appht - appuht) - (1.6 * cbdrh) ' - insht
.Left = appuwt - frmReturn.Width - (appwt - appuwt)
End With
UserForm1.Show vbModeless

' Use "comma'd" out code, and remove "- (1.6 * cbdrh)" for a worksheet
(only, not a chart) with column and row
' headings, this will avoid obscuring these headings
' I have the Drawing toolbar and sheet tabs visible at the bottom, Main menu
bar, formula bar and two rows of toolbars/commandbars visible at the top
' the code as it is works for both sheets and charts
End Sub
****************************************************************************
**

Thanks for everyone's help, I always learn so much everytime I ask a
question !

How can you work out the height of the formula bar, status bar, and column
headings with code? Knowing this would help a great deal.

Regards

Joe


Hi Joe 90;
Roughly and simply:

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
With Application
.WindowState = xlMaximized
Dim W&: W = .Width
' Zoom factor (1024 pixels = 768 points)
Me.Zoom = CInt(100 * W / 768)
Me.Top = .Height - .UsableHeight - 22
Me.Left = W - Me.Width - 19
End With
End Sub

Regards
MP

"Joe 90" <[email protected](remove silly spam)> a écrit dans le
message de
 
Top