Form Resize

[Suzette]

I can't seem to find a way to make a form resizable in VBA. Anyone out
there with an idea... or can point out my stupidity?

Thanks

Suzette

 

[Jake Marx]

Hi Suzette,

UserForms are not as customizable as VB Forms, so you can't make them
sizable without using API calls. There's an example of how to do it on
Stephen Bullen's site:

http://www.bmsltd.co.uk/Excel/SBXLPage.asp [formfun.zip]

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[steve]

Suzette,

Not sure what you want, but this will size the form to fit the entire screen

Private Sub UserForm_Initialize()
With Application
Me.Top = .Top
Me.Left = .Left
Me.Height = .Height
Me.Width = .Width
End With
End Sub

 

[Ken Macksey]

Hi

There is a freeware control available at www.VBusers.com that will allow
you to resize a VBA form and all controls on it with minimal code.
Look for FlexGrabberE in the downloads section.

HTH

Ken

 

[Vasant Nanavati]

Hi Suzette:

Alternatively, try the following API-based routine:

Option Explicit

Private Declare Function ReleaseCapture Lib "user32" () As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As _
Any) As Long

Private Const WM_NCLBUTTONDOWN = &HA1
Private Const HTBOTTOMRIGHT = 17
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
_
lpClassName As String, ByVal lpWindowName As String) As Long

Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As _
Integer, ByVal X As Single, ByVal Y As Single)
Dim lngHwnd As Long
If X >= Me.Width - 10 Then
If Y >= Me.Height - 30 Then
lngHwnd = FindWindow(vbNullString, Me.Caption)
ReleaseCapture
SendMessage lngHwnd, WM_NCLBUTTONDOWN, HTBOTTOMRIGHT, ByVal 0&
End If
End If
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As _
Integer, ByVal X As Single, ByVal Y As Single)
If X >= Me.Width - 10 Then
If Y >= Me.Height - 30 Then
Me.MousePointer = fmMousePointerSizeNWSE
'Unfortunately, in Excel 2002, the pointer doesn't change shape until
after you click the
'bottom right-hand corner of the UserForm. It worked just fine in Excel
97.
End If
Else
Me.MousePointer = fmMousePointerDefault
End If
End Sub

Regards,

Vasant.

 

[Alan T]

Really nice little tool!!!

I'm looking for a bit of help though. I've used the following code and
it does what I am looking for. However on my userform I have 75 labels,
45 checkboxes, 50 Textboxes, 4 multipages and 9 frames. How can I write
the code in such a way that I can modify the size of each element, but
without the need to list each element separately within the code.
Apologies if this is a rather simple question, but I'm only recently
started using VBA.

Private Sub FlexGrabberE1_ResizeComplete(WidthFactor As Single,
HeightFactor As Single)

With Me
With .Label1
..Left = .Left * WidthFactor
..Width = .Width * WidthFactor
..Top = .Top * HeightFactor
..Height = .Height * HeightFactor
End With
With .MultiPage1
..Left = .Left * WidthFactor
..Width = .Width * WidthFactor
..Top = .Top * HeightFactor
..Height = .Height * HeightFactor
End With
With .TextBox1
..Left = .Left * WidthFactor
..Width = .Width * WidthFactor
..Top = .Top * HeightFactor
..Height = .Height * HeightFactor
End With
End With

 

[DennisE]

Alan,
Here's how I would do it:

Sub ChangeControlSize(WidthFactor As Single, HeightFactor As Single)

Dim ctl As Control
For Each ctl in UserForm1
ctl.Left = ctl.Left * WidthFactor
ctl.Width = ctl.Width * WidthFactor
ctl.Top = ctl.Top * HeightFactor
ctl.Height = ct.Height * HeightFactor
Next
End Sub

Then shrink the User Form itself by applying the Width and Height factors to
it.

-- Dennis Eisen

 

[Andrew Baker]

Try:

Private Sub FlexGrabberE1_ResizeComplete(WidthFactor As Single,
HeightFactor As Single)
Dim oControl As Control
For Each oControl In Me.Controls
Select Case TypeName(oControl)
Case "Label"
oControl.Left = oControl.Left * WidthFactor
oControl.Width = oControl.Width * WidthFactor
oControl.Top = oControl.Top * HeightFactor
oControl.Height = oControl.Height * HeightFactor
Case "CommandButton"
oControl.Top = Me.InsideHeight - oControl.Height - 3
End Select
Me.Repaint
Next
End Sub

PS. I have updated the download to include this code

regards,
andrew baker
www.vbusers.com