Resize a Userform

Of course I tried with the Width and Height form properties, but, for I do not know what reason, they do not modify as expected. They are a little smaller than necessary and a kind of status bar label does not show as it should be...

Now, in order to leave the discussion being a little cleverer, can you explain what this 'resizable' in discussion means?

To make the form (manualy) resizable during its life?
 
Last edited:
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Strange ! that shouldn't happen because the project has a reference to the IAccessibility library by default .

Anyway, try this ;

Code:
Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc [B][COLOR=#ff0000]As Any[/COLOR][/B] , phwnd As LongPtr) As Long

Now the form is clearly resizable! I mean, I used to have the window handler like meHwnd, for a different reason and I used it in your code functions, because it was missing. But except 'GetWindowLong' which was nicely included in 'SetWindowLong' and I did not see it...
 
Last edited:
Upvote 0
Now the form is clearly resizable!

Great !

If you want to only resize the form when you change the zoom of the userform as you described in post 18 you could define a custom Property called CustomZoom as follows :

In the UserForm Module :
Code:
Option Explicit

Private lCustomZoom As Long


Public Property Get CustomZoom() As Long
   CustomZoom = IIf(lCustomZoom = 0, Me.Zoom, lCustomZoom)
End Property

Public Property Let CustomZoom(ByVal ZoomFactor As Long)
    If ZoomFactor <> Me.Zoom Then
        Me.Zoom = ZoomFactor
        Me.Width = Me.Width * ZoomFactor / 100
        Me.Height = Me.Height * ZoomFactor / 100
    End If
    lCustomZoom = ZoomFactor
End Property


And then you set the zoom from the commandbutton as follows (Instead of Me.Zoom=80) :
Code:
Private Sub CommandButton1_Click()
    Me.CustomZoom = 80
End Sub
 
Last edited:
Upvote 0
It practically does exactly what directly writing the ratio does...
But I tested it and does the same.
Using the next code:
Code:
If Not boolSmall Then
        'Me.Zoom = 80: Me.width = initWidth * 80 / 100: Me.height = initHeight * 80 / 100
        Me.CustomZoom = 80
        boolSmall = True
 Else
        'Me.Zoom = 100: Me.width = initWidth: Me.height = initHeight
        Me.CustomZoom = 100
        boolSmall = False
 End If
the form height and width do not respect the ratio. They are a little smaller and if I repeat the steps from some times, the button disappears because of the margins which are too small to include it...
It is true, I am playing with a CorelDRAW form (VBA7, Win64, Windows 10 proffessional) and theoretically it shouldn't matter...
 
Last edited:
Upvote 0
the form height and width do not respect the ratio. They are a little smaller and if I repeat the steps from some times, the button disappears because of the margins which are too small to include it...

That's true.

It only gives approximate results.

I guess, one would need to use the windows API (rather complicated) , to get accurate results.
 
Last edited:
Upvote 0
I rapidly built such a form in Excel and it behaves exactly like in Corel...
I saw an Excel thread, an old one (from 2007 I think) where somebody recommended the Leith Ross code to do this 'job' with enough accuracy...
'this job' meaning to modify the form dimensions according to the needed ration.
So, I looked for the code and its author and asked...
Unfortunately, the one making the suggestion didn't know what the code does and I went blind on his suggestion.
Anyhow, I learned something and this is the most important issue!

Thanks!

I would just suggest to include the form window handle catching in your code. Maybe somebody will need it and not fully understand...
 
Last edited:
Upvote 0
I would just suggest to include the form window handle catching in your code. Maybe somebody will need it and not fully understand...

The window handle is retrieved with the WindowFromAccessibleObject API function in its second argument.

The above API, is more accurate than using GetActiveWindow or GetForeGroundWindow etc ... as in some rare situations, the active window may not be the userform.

Have you tried the code I posted in the link ? It is much better because the controls in the useform as well as the font in the controls caption adjust themselves to the size of the userform.

Regards.
 
Upvote 0
Ahaaa....
Very interesting. I did not look to the function documentation and I just thought it is a function argument. I did no imagined that the handle is retrieved by the API function. I tested it in its original way and it works like a charm.

I did not tried it. I had in mind a different meaning for the 'resizable form'... And I thought it is not interesting for me, but I will test it now and I will put it in my collection of interesting code pieces.
Nice work...
 
Upvote 0
You may be intersted in this thread for adjusting the size of the controls and text along with the userform:
https://www.mrexcel.com/forum/excel-questions/1078144-autosizing-userform-display.html#post5179915

This is something I can use for the before described purpose...
I am mostly interested in the 'UserForm_Resize' Event ('AdjustSizeOfControls' procedure). It works very well for plus - minus 13% (increase or decrease). Outside of this ratio, it looks that the font adjustment is smaller than the label/textBox/etc. dimensions and not all the text is displayed, anymore... I can work with a little coeficient in the code. At the first glance, it looks that just modifying this line, it works acceptable:
Rich (BB code):
.Font.Size = Split(.Tag, "*")(4) * (Ufrm.InsideWidth) / (dInitWidth <red>* 1.018</red>)

Like general observations:
1.
When this part works, the form does not completely display all controls of its margins:
Rich (BB code):
'OPTIONAL: maximize the form full-screen upon first showing.
    '========
    PostMessage hwnd, WM_SYSCOMMAND, SC_MAXIMIZE, 0
I commented it and it shows like expected.
2.
Looking to the code, I was expected the form will also be (manually) resizable, but it is not. Ups... I remembered the 'WindowFromAccessibleObject' API function variable declarations. Let me try it. OK it works after changing in "ByVal pacc As Any".
I do not need this part and I commented "Call CreateMenu".

But this is the code to be improved in order to fit the complete Zoom problem. It looks that VBA adjust well the form sides dimensions, but its algorithm of controls adjustment is not perfect. Now I just modify (in code) the form dimensions...

Thanks for the good tip!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top