Stephen Bullen's Resize (Userform)

[stuartgb100]

Hi,

This has been recommended to me as a way to resize a userform, and to resize the controls proportionately.

I've downloaded the file, but (dumb as I am) I do not understand how to incorporate this into my userform code.

I'd be most grateful for a step-by-step guide, please.

Regards and thanks.

[Paul_Hossler]

I created a simple example with steps using Stephen's resizer

Maybe this will help

[stuartgb100]

Paul,

Many thanks for the reply and the time taken o provide an example.

I've copied the code as you instructed and then stepped through my form's initialise code
but receiving an error (Object variable or with block etc) error on the line marked XXXX below.

Code steps until the line XXX then jumps to the resize sub UserForm_Resize()
and then to resize sub FormResize() where it errors on the line
If mdWidth = 0 Then mdWidth = moForm.Width where
moform.width is set to Nothing

Here's my Initialise code:

Private Sub UserForm_Initialize()


Dim i As Integer


    Me.BackColor = RGB(249, 242, 228)
    Me.fraSheet.BackColor = RGB(241, 245, 253)
    Me.fraButtons.BackColor = RGB(241, 245, 253)
    Me.fraMail.BackColor = RGB(241, 245, 253)
    Me.tb16.BackColor = RGB(185, 211, 238)
    Me.tb17.BackColor = RGB(185, 211, 238)
    Me.cmdAdd.BackColor = RGB(255, 153, 153)
    Me.cmdDone.BackColor = RGB(255, 153, 153)
    Me.cmdCancel.BackColor = RGB(255, 153, 153)
    Me.cmdClear.BackColor = RGB(255, 153, 153)
    Me.cmdData.BackColor = RGB(255, 153, 153)
    Me.cmdSearch.BackColor = RGB(255, 153, 153)
    Me.cmdSend.BackColor = RGB(255, 153, 153)
        
    ' Set a Range variable to represent the Data
    Set rData = DATA.Cells(lOffset, 1).CurrentRegion
    ' The number of Colimns or Fields in the Data
    iCol = rData.Columns.Count
    'check that the DataBase fits the ListBox
    If iCol > 24 Then
        MsgBox "This form is not suitable for Databases with more than 24 Fields", vbCritical, FrmCap
        iCol = 24
    End If
    'populate labels & enable TextBoxes
    ' iCol is the number of active Labels & TextBoxes required
    ' Limit this run to the first 15 column headings
'    For iX = 1 To iCol
    For iX = 1 To 15
        Me("lbl" & iX).Caption = rData.Cells(1, iX).Value
        Me("lbl" & iX).BackColor = RGB(224, 238, 224)
        Me("tb" & iX).Enabled = True
        Me("tb" & iX).BackColor = lActive
    Next iX
    
    Me.lblTo.BackColor = RGB(224, 238, 224)
    Me.lblSubj.BackColor = RGB(224, 238, 224)
    Me.lblMsg.BackColor = RGB(224, 238, 224)
    Me.lblAttach.BackColor = RGB(224, 238, 224)
    
    ' When the form initialises, cmdAdd ("Add New Record") is enabled.
    ' Add the ControlTipText for "Add New Record"
    Me.cmdAdd.ControlTipText = "Create a new record by infilling the fields as necessary. When complete, click this button."
    
    
    ' Add the Company Name & Address label
    Me("lbl16").Caption = "Company Name & Address:"
    Me("lbl16").BackColor = RGB(224, 238, 224)
    Me("tb16").Enabled = True
    Me("tb16").BackColor = lActive
    
    With Me
        'width and height have been declared as Constants, _
         .Width = StartWidth + 665   'XXXXX  
        .Height = StartHeight + 350
        'we need the add button to be enabled
        .cmdAdd.Enabled = True
       .Caption = FrmCap
        'set the number of Columns to display in the listbox
        .lbxData.ColumnCount = iCol
        .cboSort.List = Application.WorksheetFunction.Transpose(rData.Rows(1))
        .cboSort.ListIndex = 0
        'don't allow ID # to be changed
        .tb1.Enabled = False
        'add the next ID #
        .tb1.Value = WorksheetFunction.Max(rData.Columns(1)) + 1
        ' Enable/disable the appropriate buttons
        ' Further work here
    End With
    
End Sub

I'd be grateful for further help please.

Regards and thank.

[Paul_Hossler]

1. Did you include this code in your UF's code module?

' A instance of our userform resizing class
Dim moResizer As New CFormResizer

'When activated, instantiate the resizer and let it set the form to be resizable
Private Sub UserForm_Activate()
    Set moResizer.Form = Me
End Sub

2. Did you include that class CFormResizer in your project?

3. Did you set the .Tag on the controls you want to resize?

4. this has a _ on it. Intentional?

'width and height have been declared as Constants, _
        .Width = StartWidth + 665 'XXXXX

Did you mean this or was it just for debug?

'width and height have been declared as Constants
.Width = StartWidth + 665

If Yes and Yes, then maybe post a WB with just the UF plus the minimum in it, or step through the demo do see. AFAIK the demo works

[Paul_Hossler]

The example I followed did not have an Initialize event. I think you need to do the Set there if you're going to maniplulate the properties

Also I think you need to disable Events if you do the initial setup

Try these and let me know

Option Explicit
Const StartWidth As Long = 200
Const StartHeight As Long = 100
' A instance of our userform resizing class

Dim moResizer As New CFormResizer

'When activated, instantiate the resizer and let it set the form to be resizable
'Private Sub UserForm_Activate()
'    Set moResizer.Form = Me
'End Sub


'Let the resizer resize the form's controls
Private Sub UserForm_Resize()
    moResizer.FormResize
End Sub


'Close the form
Private Sub btnClose_Click()
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    
    Set moResizer.Form = Me   '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
     
    'do not want the Resize event to fire  '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Application.EnableEvents = False
    With Me
        .Width = StartWidth + 100
        .Height = StartHeight + 50
    End With
    Application.EnableEvents = True
     
End Sub

[stuartgb100]

Hi Paul,

Thanks again.
Before I answer, can I double check how the Resizer code is meant to work ?

If I load my form with no tags set, the Resizer code should run and decide that no resizing is necessary ?
If, after the form loads, I then resize the form, the Resizer will pick up the different height and form widths
and run its' code to proportionally resize those controls where a tag has been set ?

Thanks.

[Paul_Hossler]

Well ... the way it seems to work for me

1. If no tags set, then you can resize the form, but the controls stay as is

2. If tags are set, then when you resize the form, the controls with tags set are proportionally resized also


I think a description of the process would be

a. Dim moResizer As New CFormResizer ---- creates an instance of the CFormResizer class named moResizer

b. Set moResizer.Form = Me ---- tells moResizer to operate on your UserForm1

c. Private Sub UserForm_Resize() ---- this is called when your UserForm1 is resized, and

d. which then calls moResizer.FormResize to do the resizing of the controls on the moResizer.Form which is your UserForm1


If you step through Resizer_Demo_2 you can trace what's happening

[stuartgb100]

Paul,

Following your Resizer 2 zip post, I did as follows:

I placed "Set moResizer.Form = Me" at the start of my userform Initialise code.

I then wrapped "Disable/Enable" code around the lines setting width & height
in the Initialise code.

I stepped through my form's initialise cade.

Despite the Disable/Enable statements, it still diverted to the Resize code (as before),
but this time stepped through with no errors and passed me back to my forms' Initialise code.

Code then continued until the line End Sub, where this error was displayed:
Run time Error 400
form already displayed
Cannot show modally

Am I getting there (or getting in deeper !) ?

Thanks again.
Regards.

[Paul_Hossler]

That happened to me before I stopped using my own .Show (like below)

Option Explicit
Sub Demo()
    Load UserForm1
'    UserForm1.Show

End Sub

I think that somewhere in Stephen's code there is already a .Show and mine doubled up and I got the error

I can try and take a look in CFormResizer, but I didn't want to mess with his vanilla code

[stuartgb100]

Paul,

Thanks.
To be clear, do I do nothing further for now ?

[Paul_Hossler]

I would have preferred to stay vanilla, but I changed Stephen's class(y) code to only call his Show when the form resizes so now ...

1. I can use my own .Show like I'm used to

Sub Demo()
    Load UserForm1
    UserForm1.Show
End Sub

2. I put the initialization logic in the UF's Initialize and not in the UF's Activate event

Private Sub UserForm_Initialize()
    Set moResizer.Form = Me
End Sub

3. Here's Demo_3 with the revised (only a little) class code. Run sub Demo or push the button to make sure it works for you. If it works, try my modified version of Stephen's class module

'***************************************************************************
'*
'* MODULE NAME:     USERFORM RESIZER CLASS
'* AUTHOR & DATE:   STEPHEN BULLEN, Office Automation Ltd
'*
'* CONTACT:         Stephen@oaltd.co.uk
'* WEB SITE:        http://www.oaltd.co.uk
'*
'* DESCRIPTION:     Handles the resizing and repositioning of controls on a userform
'*
'***************************************************************************
'phh change - 4/16/2016
'   moved hWndForm to module level
'   moved 'ShowTheWindow' code from Set Form to FormResize

'This class makes a userform resizable and handles the resizing of all the controls on the userform,
'such that their physical dimensions (e.g. size and position) change as the form is resized.
'Note that this is not a form 'magnifier', in that it does not alter font sizes.
'To specify which control(s) to resize (and how), you set the control's .Tag property at design time to
'indicate that the control's top, left, width and height should be adjusted as the form's size changes.
'
'Use the letters t, l, w and h in any order (or not at all) to state that the property should change as the form
'is resized.  Follow the property by a decimal to indicate that the control should change by a percentage of the
'form's change.
'
'For example:
'  hw           Sets the control's height and width to change with the form (e.g. if there's a single list box on the form)
'  tl           Sets the contol's top and left to change in line with the form (e.g. to keep it in the bottom-right corner)
'  w0.5         Sets the control's width to change by 0.5 that of the form's width change
'  w0.5l0.5     Sets the control's width and position to change by 0.5 that of the form's width change

[stuartgb100]

Hi Paul,
Not working for my form, I’m afraid.

Here is what I did:

I placed this code as the first line of my form’s initialise code:
Set moResizer.Form = Me

I replaced the earlier classmodule with your version 3.

In case of issues, I remmed my statements for width and height in the
Initialise code, and replaced them with settings at design time.

I then ran the code.

Seemed fine, but when displayed the form was not resizeable.
Again, I could not drag borders nor the resize handle in the lower right corner.

Regards,
Stuart.

[Paul_Hossler]

Hmmm

Can you post a small sample that demos the issue?

The _3 version seems to work for me

[stuartgb100]

Could I send a workbook privately ?

Even after editing there would be confidential information otherwise.

Regards.

[Paul_Hossler]

I don't mind, but we really don't need any data to see what's going on. Can you just delete ALL worksheets and / or data and / or macros and just leave enough to call the userform that is not resizing?


Also, did you run Resizer-Demo_3 and were you able to resize the form and controls?

Capture.JPG

[stuartgb100]

Your version 3 seemed to work for me, in that:

I could drag your form's borders to resize vertically/horizontally.
I could resize via dragging the bottom right-hand corner of the form.
I was able to set the tag parameters as I wished.

In my form, none of that worked (except setting tags).

I will post asap with an attachment.

Thanks.

[stuartgb100]

Well, I would, if I could work out how to add an attachment.

[Bob Phillips]

Stuart, are you using 64bit Excel, the code is 32bit and needs updating for 64bit.

To add an attachment, click the Advanced button.

[Paul_Hossler]

Stuart -- I never thought to ask about 64 bit Office, but I would think that since you could run the Resizer-Demo_3 OK, then you should be OK

I have 32 bit Office (running Win 10 64 bit)

[stuartgb100]

Thanks both.

I have Office 2010 (32 bit) running under Win10 64 bit.

I was using the Advanced for adding an attachment, but missed the red flag saying the file exceeded the Forum's limit.
So, trying again .......

Regards.