Resize a Userform

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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:

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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:

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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.
 

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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...
 

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,665
Messages
5,488,179
Members
407,630
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top