Position Userform In Cells Portion of Excel Only

[Ryan H]

I have several userforms, some larger than peoples screens. When the user
with a little screen clicks a button on Sheets("QUOTE") the userform opens
and it is too large to fit on their screen, thus not giving them the ability
to see all the controls on the userform.

What I want to do is before each userform is opened run code that will test
if the userform is larger than the displayed cells portion of Excel then add
both scroll bars to the userform so the user can access all controls.

I have this already, but it opens the userform at the very top of the
application screen, blocking the ribbon (2007) or menu bars (2003). I'd like
the userforms top left corner to start in the top left corner of Range("A1"),
is this possible?

Sub AdjustScreenSize()

' move user form to upper left of cells and show scoll bars
With ProductForm
If .Height > Application.Height Or .Width > Application.Width Then

' adjust form properties
.StartUpPosition = 0 ' manual
.ScrollBars = 3 ' show both scroll bars
.ScrollHeight = .Height ' scroll bar equals height of form
.ScrollWidth = .Width ' scroll bar equals width of form

.Top = ActiveSheet.Range("A1").Top
.Left = ActiveSheet.Range("A1").Left
.Height = Application.Height
.Width = Application.Width
End If
End With

End Sub


Thanks in Advance,

 

[sebastien]

Hi. A bit better but still not perfect:

In the userform:
Me.Top = Application.Top + (Application.Height - Application.UsableHeight)
Me.Left = Application.Left + (Application.Width - Application.UsableWidth)
It is not exactly on A1, you would have to substract the status bar height;
similar thing for the left property.

However, in terms of Height and Width, you can use Application.UsableHeight
and Application.UsableWidth

I hope this helps.

Sebastien