Resizing worksheet objects according to user's screen resolution

[tinkythomas]

I have a worksheet with some objects (command buttons, rectangles and text boxes) to create a basic UI. This is fine on my monitor with the resolution set at 1280x1024. However on other users monitors where the resolution is different, not all of the objets are visible.

Is it possible to determine the resolution in use and resize the objects to fit the screen?

Regards,

 

[Dryver14]

Found this on line, it returns a message box but I'm sure you can pick out the bits you need

[COLOR=blue]Option Explicit[/COLOR] 
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Workbook_Open() 
     
    [COLOR=blue]Call[/COLOR] VerifyScreenResolution 
     
[COLOR=blue]End Sub[/COLOR] 
 
 
[COLOR=blue]Option Explicit[/COLOR] 
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Declare[/COLOR] [COLOR=blue]Function[/COLOR] GetSystemMetrics Lib "user32.dll" ([COLOR=blue]ByVal[/COLOR] nIndex [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
[COLOR=blue]Const[/COLOR] SM_CXSCREEN = 0 
[COLOR=blue]Const[/COLOR] SM_CYSCREEN = 1 
 
[COLOR=blue]Sub[/COLOR] VerifyScreenResolution(Optional Dummy [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR]) 
     
    [COLOR=blue]Dim[/COLOR] x  [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
    [COLOR=blue]Dim[/COLOR] y  [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
    [COLOR=blue]Dim[/COLOR] MyMessage [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
    [COLOR=blue]Dim[/COLOR] MyResponse [COLOR=blue]As[/COLOR] VbMsgBoxResult 
     
    x = GetSystemMetrics(SM_CXSCREEN) 
    y = GetSystemMetrics(SM_CYSCREEN) 
    [COLOR=blue]If[/COLOR] x = 1024 [COLOR=blue]And[/COLOR] y = 768 [COLOR=blue]Then[/COLOR] 
    [COLOR=blue]Else[/COLOR] 
        MyMessage = "Your current screen resolution is " & x & " X " & y & vbCrLf & "This program " & _ 
        "was designed to run with a screen resolution of 1024 X 768 and may not function properly " & _ 
        "with your current settings." & vbCrLf & "Would you like to change your screen resolution?" 
        MyResponse = MsgBox(MyMessage, vbExclamation + vbYesNo, "Screen Resolution") 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
    [COLOR=blue]If[/COLOR] MyResponse = vbYes [COLOR=blue]Then[/COLOR] 
        [COLOR=blue]Call[/COLOR] Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3") 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
     
[COLOR=blue]End Sub[/COLOR]

 

[tinkythomas]

Thanks for the code snippet. I was really looking for some code that would resize the worksheet objects automatically rather than have the user change their screen resolution to suit.

Thanks for your help anyway.

Regards,