PDA

View Full Version : Stephen Bullen's Resize (Userform)



stuartgb100
04-15-2016, 09:46 AM
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
04-15-2016, 01:20 PM
I created a simple example with steps using Stephen's resizer

Maybe this will help

stuartgb100
04-16-2016, 07:42 AM
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
04-16-2016, 08:00 AM
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
04-16-2016, 08:23 AM
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
04-16-2016, 08:29 AM
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
04-16-2016, 10:57 AM
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
04-16-2016, 11:03 AM
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
04-16-2016, 11:16 AM
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
04-16-2016, 11:20 AM
Paul,

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

Paul_Hossler
04-16-2016, 11:34 AM
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
04-17-2016, 08:00 AM
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
04-17-2016, 08:02 AM
Hmmm

Can you post a small sample that demos the issue?

The _3 version seems to work for me

stuartgb100
04-17-2016, 08:24 AM
Could I send a workbook privately ?

Even after editing there would be confidential information otherwise.

Regards.

Paul_Hossler
04-17-2016, 08:54 AM
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?

15950

stuartgb100
04-17-2016, 09:16 AM
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
04-17-2016, 10:37 AM
Well, I would, if I could work out how to add an attachment.

Bob Phillips
04-17-2016, 11:36 AM
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
04-17-2016, 12:25 PM
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
04-17-2016, 01:26 PM
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.

Paul_Hossler
04-17-2016, 03:56 PM
What I THINK was happening is ..

1. Stephen's code uses the user form's .Caption to identify the correct handle

2. When After you set moResizer, you changed the UF caption

3. In the UF Initialize move the .Caption = line from where it is to before you do the Set moResizer =




Private Sub UserForm_Initialize()
Dim i As Integer
Me.Caption = FrmCap
Set moResizer.Form = Me


' etc.



That seems to allow resizing

I only added a 'hw' tag for the circled control, and it adjusts

15952

However, I saw that you're already using the .Tag in a number of controls (looks like a column number), and I think your form would not lend itself to resizing. It looks fine as is.

You might want to re-think resizing the form

Maybe one of the more experienced people here will have a better suggestion, but it was an interesting Sunday afternoon project

stuartgb100
04-17-2016, 10:13 PM
Paul,

Yes, that was it. Works fine now. Many thanks.

As for the existing .Tag values: you are correct, they link the textboxes to columns in the database.

I wonder if the following might work, with the proviso being that the Resizer can be turned On/Off by a button on the form:

Form loads with Resizer disabled
User clicks the Resize button
Code loops through all the form's controls and puts the .Tag values into an array, and at the same time sets the .Tag values to hw (for example)
The Resizer code is then enabled
User resizes the form for their display
Code replaces the .Tag values with those held in the array
The Resizer code is then disabled

Many thanks again for all your time and trouble.

Best Regards.