VBA Userform full screen scroll bars

[blossomthe2nd]

Hi Experts

I designed a few Userforms on my PC that has a high resolution , the USERFORM has a code that makes it full screen.

Dim lngWinState As XlWindowState
     
    With Application
        .ScreenUpdating = False
        lngWinState = .WindowState
        .WindowState = xlMaximized
        Me.Move 0, 0, .Width, .Height
        .WindowState = lngWinState
        .ScreenUpdating = True
    End With

But my issue is that other users have a lower resolution so when the userform opens on their PCs they can not see the ends and right side of the form - it also does not allow the form to scroll across.

Can anyone advise a solution where either

a) the code full screens to the users resolution
b) or where we can ahve scroll bars so the users may move the USerForm to see all questions

Thanks guys

Anne

 

[RoryA]

Personally I would recommend that you redesign the form to work with a lower resolution.

 

[blossomthe2nd]

Personally I would recommend that you redesign the form to work with a lower resolution.

Hi Rory

See thats the issue at the absic, this team uses a variety of different resolutions so if i select a lower resolution it may suit some of the team but not everybody - is it not possible to have a variable sized Userform ? OR put scroll bars ?

Thanks

A

 

[dmt32]

Anne,
another suggestion would be to create two versions of your form(s) & then test what resolution user has & display accordingly.

following code (published by another author) may help you do this. Place 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

Hope helpful

Dave

 

[RoryA]

You can make a resizeable form but it's quite a lot of work (have a look at FormFun for example) or set the form's scrollbars property and use the scrollheight and scrollwidth properties to determine the full size of the form (they need to be larger than the height and width properties), but standard practice is to develop for a standard (not too high) resolution such as 1024 x 768 which allows all the form to be on screen for everyone.

 

[blossomthe2nd]

Dave and Rory

Thanks to both for your suggestions - I'm going to have a little play around to see what works best -

I'll update with what worked for me

Happy Holidays

Anne