I have many different users for one of my workbooks. The workbook has many
different sized userforms. Some userforms are too large for the users
screen. Is there a way for VBA to automatically adjust the userform to fit
in the users screen properly like other software does?
--
Cheers,
Ryan

Try the below

Private Sub UserForm_Initialize()
With Me
.Height = Application.Height
.Width = Application.Width
.Left = 0
.Top = 0
End With
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Ryan H" wrote:

> I have many different users for one of my workbooks. The workbook has many
> different sized userforms. Some userforms are too large for the users
> screen. Is there a way for VBA to automatically adjust the userform to fit
> in the users screen properly like other software does?
> --
> Cheers,
> Ryan

I don't think I need to expand the userform to fit the screen. I need to
shrink the userform. Any ideas?
--
Cheers,
Ryan


"Jacob Skaria" wrote:

> Try the below
>
> Private Sub UserForm_Initialize()
> With Me
> .Height = Application.Height
> .Width = Application.Width
> .Left = 0
> .Top = 0
> End With
> End Sub
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Ryan H" wrote:
>
> > I have many different users for one of my workbooks. The workbook has many
> > different sized userforms. Some userforms are too large for the users
> > screen. Is there a way for VBA to automatically adjust the userform to fit
> > in the users screen properly like other software does?
> > --
> > Cheers,
> > Ryan

Here is a variation:

Private Sub UserForm_Initialize()
With Me
If (.Height > Application.Height) Or (.Width > Application.Width) Then
.Height = Application.Height * 0.9
.Width = Application.Width * 0.9
.Left = 0
.Top = 0
End If
End With
End Sub

Please note that this doesn't shrink everything on the form proportionately
to fit; it truncates part of the form on the right and/or bottom to make it
fit on the screen. You might do better to check the form size vs. screen area
as above and notify your users to increase their screen resolution if
necessary. Or, build your form to a "lowest common denominator" resolution.

Hope this helps,

Hutch

"Ryan H" wrote:

> I don't think I need to expand the userform to fit the screen. I need to
> shrink the userform. Any ideas?
> --
> Cheers,
> Ryan
>
>
> "Jacob Skaria" wrote:
>
> > Try the below
> >
> > Private Sub UserForm_Initialize()
> > With Me
> > .Height = Application.Height
> > .Width = Application.Width
> > .Left = 0
> > .Top = 0
> > End With
> > End Sub
> >
> > --
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Ryan H" wrote:
> >
> > > I have many different users for one of my workbooks. The workbook has many
> > > different sized userforms. Some userforms are too large for the users
> > > screen. Is there a way for VBA to automatically adjust the userform to fit
> > > in the users screen properly like other software does?
> > > --
> > > Cheers,
> > > Ryan

If some users are having a screen overflow, it could be because they have
their screen resolution set to an unconventional setting. If so, they are
forcing you to alter a standard feature to meet their non-standard whims. I
would check their settings before trying to accommodate them.


"Ryan H" <(E-Mail Removed)> wrote in message
news:EE608E05-7BB1-4E28-AB8F-(E-Mail Removed)...
>I don't think I need to expand the userform to fit the screen. I need to
> shrink the userform. Any ideas?
> --
> Cheers,
> Ryan
>
>
> "Jacob Skaria" wrote:
>
>> Try the below
>>
>> Private Sub UserForm_Initialize()
>> With Me
>> .Height = Application.Height
>> .Width = Application.Width
>> .Left = 0
>> .Top = 0
>> End With
>> End Sub
>>
>> --
>> If this post helps click Yes
>> ---------------
>> Jacob Skaria
>>
>>
>> "Ryan H" wrote:
>>
>> > I have many different users for one of my workbooks. The workbook has
>> > many
>> > different sized userforms. Some userforms are too large for the users
>> > screen. Is there a way for VBA to automatically adjust the userform to
>> > fit
>> > in the users screen properly like other software does?
>> > --
>> > Cheers,
>> > Ryan

That code does get me thinking and maybe I can think of something, but all
that does is cut off the controls on the userform if the userform is too big.
I think I need to adjust the size of the userform with the zoom property.
--
Cheers,
Ryan


"Tom Hutchins" wrote:

> Here is a variation:
>
> Private Sub UserForm_Initialize()
> With Me
> If (.Height > Application.Height) Or (.Width > Application.Width) Then
> .Height = Application.Height * 0.9
> .Width = Application.Width * 0.9
> .Left = 0
> .Top = 0
> End If
> End With
> End Sub
>
> Please note that this doesn't shrink everything on the form proportionately
> to fit; it truncates part of the form on the right and/or bottom to make it
> fit on the screen. You might do better to check the form size vs. screen area
> as above and notify your users to increase their screen resolution if
> necessary. Or, build your form to a "lowest common denominator" resolution.
>
> Hope this helps,
>
> Hutch
>
> "Ryan H" wrote:
>
> > I don't think I need to expand the userform to fit the screen. I need to
> > shrink the userform. Any ideas?
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Try the below
> > >
> > > Private Sub UserForm_Initialize()
> > > With Me
> > > .Height = Application.Height
> > > .Width = Application.Width
> > > .Left = 0
> > > .Top = 0
> > > End With
> > > End Sub
> > >
> > > --
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "Ryan H" wrote:
> > >
> > > > I have many different users for one of my workbooks. The workbook has many
> > > > different sized userforms. Some userforms are too large for the users
> > > > screen. Is there a way for VBA to automatically adjust the userform to fit
> > > > in the users screen properly like other software does?
> > > > --
> > > > Cheers,
> > > > Ryan

I did that. They have a lap top and the resolution is as high as it can go,
1280x800 @ 96 dpi. Would that be a problem?
--
Cheers,
Ryan


"JLGWhiz" wrote:

> If some users are having a screen overflow, it could be because they have
> their screen resolution set to an unconventional setting. If so, they are
> forcing you to alter a standard feature to meet their non-standard whims. I
> would check their settings before trying to accommodate them.
>
>
> "Ryan H" <(E-Mail Removed)> wrote in message
> news:EE608E05-7BB1-4E28-AB8F-(E-Mail Removed)...
> >I don't think I need to expand the userform to fit the screen. I need to
> > shrink the userform. Any ideas?
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Jacob Skaria" wrote:
> >
> >> Try the below
> >>
> >> Private Sub UserForm_Initialize()
> >> With Me
> >> .Height = Application.Height
> >> .Width = Application.Width
> >> .Left = 0
> >> .Top = 0
> >> End With
> >> End Sub
> >>
> >> --
> >> If this post helps click Yes
> >> ---------------
> >> Jacob Skaria
> >>
> >>
> >> "Ryan H" wrote:
> >>
> >> > I have many different users for one of my workbooks. The workbook has
> >> > many
> >> > different sized userforms. Some userforms are too large for the users
> >> > screen. Is there a way for VBA to automatically adjust the userform to
> >> > fit
> >> > in the users screen properly like other software does?
> >> > --
> >> > Cheers,
> >> > Ryan
>
>
>

It is one of the possibilities. Depends on the size of the monitor and what
the normal resolution should be. I am just thinking that trying to solve
the problem with code may not be the best answer, although it could probably
be done. If the resolution is set properly, they should get the same view
you had originally. Of course, this assumes your resolution is also set
properly.


"Ryan H" <(E-Mail Removed)> wrote in message
news:824DD87E-5711-4D80-A8CD-(E-Mail Removed)...
>I did that. They have a lap top and the resolution is as high as it can
>go,
> 1280x800 @ 96 dpi. Would that be a problem?
> --
> Cheers,
> Ryan
>
>
> "JLGWhiz" wrote:
>
>> If some users are having a screen overflow, it could be because they have
>> their screen resolution set to an unconventional setting. If so, they
>> are
>> forcing you to alter a standard feature to meet their non-standard whims.
>> I
>> would check their settings before trying to accommodate them.
>>
>>
>> "Ryan H" <(E-Mail Removed)> wrote in message
>> news:EE608E05-7BB1-4E28-AB8F-(E-Mail Removed)...
>> >I don't think I need to expand the userform to fit the screen. I need
>> >to
>> > shrink the userform. Any ideas?
>> > --
>> > Cheers,
>> > Ryan
>> >
>> >
>> > "Jacob Skaria" wrote:
>> >
>> >> Try the below
>> >>
>> >> Private Sub UserForm_Initialize()
>> >> With Me
>> >> .Height = Application.Height
>> >> .Width = Application.Width
>> >> .Left = 0
>> >> .Top = 0
>> >> End With
>> >> End Sub
>> >>
>> >> --
>> >> If this post helps click Yes
>> >> ---------------
>> >> Jacob Skaria
>> >>
>> >>
>> >> "Ryan H" wrote:
>> >>
>> >> > I have many different users for one of my workbooks. The workbook
>> >> > has
>> >> > many
>> >> > different sized userforms. Some userforms are too large for the
>> >> > users
>> >> > screen. Is there a way for VBA to automatically adjust the userform
>> >> > to
>> >> > fit
>> >> > in the users screen properly like other software does?
>> >> > --
>> >> > Cheers,
>> >> > Ryan
>>
>>
>>

Ryan,
I found this on the web sometime ago - never used it myself but may (or may
not) be useful to you. Paste all in standard module.

Type RECT
x1 As Long
y1 As Long
x2 As Long
y2 As Long
End Type
' NOTE: The following declare statements are case sensitive.
Declare Function GetDesktopWindow Lib "user32" () As Long
Declare Function GetWindowRect Lib "user32" _
(ByVal hWnd As Long, rectangle As RECT) As Long
'*****************************************************************
' FUNCTION: GetScreenResolution()
'
' PURPOSE:
' To determine the current screen size or resolution.
'
' RETURN:
' The current screen resolution. Typically one of the following:
' 640 x 480
' 800 x 600
' 1024 x 768
'
' AUTHOR:
' Tom Ogilvy
'*****************************************************************
Public Function GetScreenResolution() As String
Dim R As RECT
Dim hWnd As Long
Dim RetVal As Long

hWnd = GetDesktopWindow()
RetVal = GetWindowRect(hWnd, R)
GetScreenResolution = (R.x2 - R.x1) & "x" & (R.y2 - R.y1)
End Function

Sub test()
MsgBox GetScreenResolution

End Sub
--
jb


"Ryan H" wrote:

> I did that. They have a lap top and the resolution is as high as it can go,
> 1280x800 @ 96 dpi. Would that be a problem?
> --
> Cheers,
> Ryan
>
>
> "JLGWhiz" wrote:
>
> > If some users are having a screen overflow, it could be because they have
> > their screen resolution set to an unconventional setting. If so, they are
> > forcing you to alter a standard feature to meet their non-standard whims. I
> > would check their settings before trying to accommodate them.
> >
> >
> > "Ryan H" <(E-Mail Removed)> wrote in message
> > news:EE608E05-7BB1-4E28-AB8F-(E-Mail Removed)...
> > >I don't think I need to expand the userform to fit the screen. I need to
> > > shrink the userform. Any ideas?
> > > --
> > > Cheers,
> > > Ryan
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > >> Try the below
> > >>
> > >> Private Sub UserForm_Initialize()
> > >> With Me
> > >> .Height = Application.Height
> > >> .Width = Application.Width
> > >> .Left = 0
> > >> .Top = 0
> > >> End With
> > >> End Sub
> > >>
> > >> --
> > >> If this post helps click Yes
> > >> ---------------
> > >> Jacob Skaria
> > >>
> > >>
> > >> "Ryan H" wrote:
> > >>
> > >> > I have many different users for one of my workbooks. The workbook has
> > >> > many
> > >> > different sized userforms. Some userforms are too large for the users
> > >> > screen. Is there a way for VBA to automatically adjust the userform to
> > >> > fit
> > >> > in the users screen properly like other software does?
> > >> > --
> > >> > Cheers,
> > >> > Ryan
> >
> >
> >

Hi Ryan

Please refer the below thread

http://www.microsoft.com/office/comm...d-31817f6269a4


You can also set this to Application.UsableWidth

Private Sub UserForm_Initialize()
With Me
.Height = Application.UsableHeight
.Width = Application.UsableWidth
.Left = 0
.Top = 0
End With
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Ryan H" wrote:

> I don't think I need to expand the userform to fit the screen. I need to
> shrink the userform. Any ideas?
> --
> Cheers,
> Ryan
>
>
> "Jacob Skaria" wrote:
>
> > Try the below
> >
> > Private Sub UserForm_Initialize()
> > With Me
> > .Height = Application.Height
> > .Width = Application.Width
> > .Left = 0
> > .Top = 0
> > End With
> > End Sub
> >
> > --
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Ryan H" wrote:
> >
> > > I have many different users for one of my workbooks. The workbook has many
> > > different sized userforms. Some userforms are too large for the users
> > > screen. Is there a way for VBA to automatically adjust the userform to fit
> > > in the users screen properly like other software does?
> > > --
> > > Cheers,
> > > Ryan