Resize subforms and controls with vba

[Ringhal]

Hi

I have been struggling with this for a few days now, and need some help. I've created an Access database and this is distributed throughout the company. Everybody uses different screen sizes and pixel resolutions and I wanted to create a short code the can resize the various forms and controls. Below is the code I used that works for some controls and forms, but not everything. After finding out that Visual Basic uses twips instead of centimeters (my default unit of measure), I converted these values where needed.

Here's what I have: A multipage form (or Tab Control) that has 5 pages with 1 subform per page (plus other buttons and boxes). Each of the subforms need to be the same size.



Sub Test()
'the default unit of measurement in Visual Basic is twips
'1 centimeter = 566.9291338583 twip
Dim i As Long
Dim CtrlForm, CtrlPage

For i = 2 To 6
Select Case i
Case 1
Set CtrlPage = Forms![frmForm A]![MultiPage A]
GoSub frmName_multipage
Case 2
Set CtrlForm = Forms![frmForm A]![subform A]
Case 3
Set CtrlForm = Forms![frmForm A]![subform B]
Case 4
Set CtrlForm = Forms![frmForm A]![subform C]
Case 4
Set CtrlForm = Forms![frmForm A]![subform D]
Case 5
Set CtrlForm = Forms![frmForm A]![subform E]
Case 6
Set CtrlForm = Forms![frmForm A]![subform F]
Case 2 To 6
GoSub frmName_subform
Case Else
Exit For
End Select
Next i
Exit Sub

frmName_subform:
With CtrlForm
.Left = 56 '0.10 cm
.Width = 10204 '18 cm
End With
Return

frmName_multipage:
With CtrlPage
.Left = 0 '0.00 cm
.Width = 13606 '24 cm
End With
Return

End Sub

[jonh]

Not sure I follow.

You say you want to resize controls to fit different screen sizes, but then you talk about units of measure and you've hard coded the values.

If you want a control to resize to fit the screen, the unit of measure doesn't matter; the child object is just resized relative to its parent. But if that is what you want and you have a fairly up to date version of Access, you shouldn't need code anyway...
(Form Design, select control) > Arrange > Size > Anchoring > Stretch down and across.

[Ringhal]

The database was originally developed in 2000 and after trying your steps, the subform ended up worse than before, it went over some needed buttons and boxes and the width was smaller. The only thing I need to change is the width. I tested with Access 2013 after upgrading the database to the new version.

The reason for hard carding, was because I need to only run this code once for each database. Also, in many of the database the Full Menu Bar is hidden.

[jonh]

Best thing to do is upload an image of a screen that looks ok on one size screen and wrong on another.
The code you posted looks like it sets things to a certain size, which is kinda pointless because you can do that in the form properties (which on my Access is in cm).
If you want something to resize due to different screen res, you would usually do the same thing for resizing the form or application window when not maximised, so you would run the code in the form's resize event.

[InLaNoche]

There are pre-set standards for resolution, and I am thinking this is what you are trying to adust for. As stated above, screen size does not matter when fitting data on the screen, you look at the resolution set, and the font size. The best bet would be to create a setup per resolution used. There should not be more that 4 that you would need to work with. Above the highest should not matter. I'm sure VBA can pole for the screen res (saw it in a thread somewhere).

[Ringhal]

Below is one of the forms that need to be resized, all the others are similar. The datasheet in the form needs to be resized as wide as the screen. Most resolutions are the laptop standard of 1366 x 768, but the others are also possible.

13467

[jonh]

All the options you need are on the anchoring menu. Stretch down and accross for the tab control and subs. Bottom left for the other stuff.

[Ringhal]

I must be doing something seriously wrong because none of the anchoring/sizing options are working or doing anything useful.