Adjusting User Form size to coincide with screen resolution

[BexleyManor]

Hi All,

I have a workbook with a user form that opens full screen when run. My problem is the user form is designed for 1024x768 and as the workbook is now being used on machines with 800x600 res the user form looks, well, wappy!!

I've managed to write some code that checks the current screen res (see below), however I can't for the life of me work out how to tie this in to resizing the user form depending on the res it's being used at.

Any ideas folks??

Option Explicit
 Private Const SM_CXSCREEN = 0
 Private Const SM_CYSCREEN = 1
 Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
 
 Private Sub UserForm_Activate()
 Dim width As Long, height As Long
width = GetSystemMetrics(SM_CXSCREEN)
 height = GetSystemMetrics(SM_CYSCREEN)
MsgBox "Screen Width = " & width & Chr(10) & "Screen Height = " & height
 End Sub

[XL-Dennis]

Hi,

Per se I would say that the design of the userform is not good if we need to consider the screen-resolution.

Instead of playing around with this issue I strongly suggest that You re-design the userform. You can divide it into several sub-forms as well as view/hide some parts of it by resizing it via buttons.

Kind regards,
Dennis

[Jacob Hilderbrand]

I use this to check the screen resolution and allow the user to change their own resolution.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=32

You can also change the zoom setting on the userform to make it change depending on the size of the screen.

Or you can have each control on the userform move based on the left/width/top/height of the userform/application. This is a pain tho.

Or you can make multiple userforms and show them based on the screen resolution.

[BexleyManor]

Hi Dennis,

I appreciate what you say however I simply do not have enough hours in my day to start re-designing the user form, hence my thoughts on using code to provide a 'quick fix'.

[Ivan F Moala]

I would let the Windows system determine this for you via the Addition of the Min/Max buttons.......to do this have a look here;

http://www.xcelfiles.com/Userform_TaskBar.html

your'll need to change one of the Flags in the underlying code

lStyle = lStyle Or WS_POPUP Or WS_MAXIMIZEBOX       '// and MaximizeBox

[BexleyManor]

DRJ,

Thanks for the input. The link in itself provides a nice solution however you have no idea how numb my users are, they fear change!! If prompted to change the resolution manually they will more than likely take to their PC with a hammer on the grounds 'It spoke to me in tounges', I kid you not!!

[XL-Dennis]

OK, I got Your point and check out Ivan's nice approach

...how numb my users are

I believe we need to respect it and consider this aspect in all solutions.

Kind regards,
Dennis

[BexleyManor]

Ivan,
I'm just looking at your code now, forgive me if I'm a little slow in responding, I'm still 'newbie' status and the whole API stuff starts my head fizzing. It took me about two hours to work out how to get the screen res API!!

Please bear with me....

[BexleyManor]

Well, that's the weekend over and back to the grind. Having taken a good look at Ivan's code I'm about as clear as mud!!

[Ivan F Moala]

Hi BexleyManor

What is it that you have trouble with....let me know and I'll try and walk you through it

[Anne Troy]

Any news on this one, Bexley?

[BexleyManor]

Still mulling over this one. Been kinda dragged off on a tangent to finish off another project first. Hopefully can get back to it later this week.....

[BexleyManor]

Hi all,

Just had a moment to look over Ivan's fine code once again and in theory, yes, I could probably adapt it to solve my problem. However, me being me I'd still hoped for that ten line 'quick & dirty fix' piece of code that I could implement!!

Maybe I'll have to bite the bullet on this one and burn the midnight oil??