Access 2010 VBA 表单 - 自动调整表单大小

Question

提问by Padawan

I have complete my form to use around the office, however, when opened on different computers the form doesnt resize.Instead, the scroll bar appears. How can i make the form and controls automatically resize ?

我已经完成了可以在办公室使用的表单，但是，在不同的计算机上打开时，表单不会调整大小。相反，会出现滚动条。如何使表单和控件自动调整大小？

Answer 1

回答by

Here is some VBA code you could add to your form that will keep the form looking the same no matter how large or small the user has made the window on their monitor or what their monitor resolution is.

这里有一些 VBA 代码，你可以添加到你的表单中，无论用户在他们的显示器上制作的窗口有多大或多小，或者他们的显示器分辨率是多少，这些代码都会使表单看起来相同。

Also you can make the text larger or smaller by holding the Ctrlkey and scrolling the mouse wheel up and down (or, alternatively, holding the Shiftkey and hitting the +key or the -key.)

您也可以通过按住Ctrl键并上下滚动鼠标滚轮来放大或缩小文本（或者，按住Shift键并+按下键或-键。）

To use this functionality, just open Access and open your form in design view. First, right-click on the image of the form and add the Form Header/Footer.

要使用此功能，只需打开 Access 并在设计视图中打开您的表单。首先，右键单击表单的图像并添加Form Header/Footer.

If you don't add the header and footer to the form, the code below will error out. However, you can shrink the height of both the header and the footer to nothing if you don't want them to appear on your form.

如果你没有在表单中添加页眉和页脚，下面的代码会出错。但是，如果您不希望页眉和页脚出现在表单上，您可以将它们的高度缩小到零。

Select the Form itself by clicking the little box at the top left of the form, just below the tab:

通过单击表单左上角的小框选择表单本身，就在选项卡下方：

This will make sure we are looking at the properties for the form itself when we view the Property Sheet.

这将确保我们在查看Property Sheet.

To view the Property Sheetfor the form (if it isn't visible already), hold the Altkey and press the Enterkey.

要查看Property Sheet表单（如果它不可见），请按住Alt键并按下Enter键。

Choose the Eventtab.

选择Event选项卡。

You'll then need to add the literal text [Event Procedure]to the following five events behind the form itself:

然后，您需要将文字文本添加[Event Procedure]到表单本身后面的以下五个事件中：

On Load
On Key Up
On Key Down
On Resize
On Mouse Wheel

负载
按键时
按键按下
调整大小
在鼠标滚轮上

You can either type the literal text [Event Procedure]into the text box next to these events, or click the ellipsis (...) button next to each event and choose Code Builderfrom the pop up menu.

您可以在[Event Procedure]这些事件旁边的文本框中键入文字文本，也可以单击每个事件旁边的省略号 (...) 按钮并Code Builder从弹出菜单中进行选择。

It will look something like this:

它看起来像这样：

...

Also, at the bottom of the list of events, you'll also need to change the Key Previewproperty to Yes:

此外，在事件列表的底部，您还需要将Key Preview属性更改为Yes：

Finally, you'll probably want to turn Scroll Barsoff on the form so that they don't overlap any content. To do this, go to the Formattab of the Property Sheetfor your form in design view and change the Scroll Barsproperty to Neither.

最后，您可能希望Scroll Bars关闭表单，以免它们与任何内容重叠。为此，请转到设计视图中表单的Format选项卡Property Sheet并将Scroll Bars属性更改为Neither。

Now, to add the VBA code, hold Altand hit F11to view the VBA editor.

现在，要添加 VBA 代码，按住Alt并点击F11查看 VBA 编辑器。

Once inside the VBA editor, double click on the Form_YourFormNameoption under the Microsoft Access Class Objectsfolder:

进入 VBA 编辑器后，双击文件夹Form_YourFormName下的选项Microsoft Access Class Objects：

If you do not see the Microsoft Access Class Objectsfolder, then go back to the form in design view and click the ellipsis (...) next to the literal text [Event Procedure]on any of the events you just modified.

如果您没有看到该Microsoft Access Class Objects文件夹，则返回到设计视图中的表单并单击[Event Procedure]您刚刚修改的任何事件的文字旁边的省略号 (...) 。

This will take you back to the VBA editor and you should now be inside the Form_YourFormNamecode area. There will already be some code there, but you can erase all of it before proceeding to the next step.

这将带您返回 VBA 编辑器，您现在应该位于Form_YourFormName代码区域内。那里已经有一些代码，但您可以在继续下一步之前擦除所有代码。

Then in the main part of the screen on the right, just copy and paste the code below and you're done.

然后在右侧屏幕的主要部分，只需复制并粘贴下面的代码即可完成。

Option Compare Database
Option Explicit


'Set an unchangeable variable to the amount (10% for example) to increase or
'decrease the font size with each zoom, in or out.
Const FONT_ZOOM_PERCENT_CHANGE = 0.1


'Create the fontZoom and ctrlKeyIsPressed variables outside of
'the sub definitions so they can be shared between subs
Private fontZoom As Double
Private ctrlKeyIsPressed As Boolean


'Create an enum so we can use it later when pulling the data out of the "Tag" property
Private Enum ControlTag
    FromLeft = 0
    FromTop
    ControlWidth
    ControlHeight
    OriginalFontSize
    OriginalControlHeight
End Enum


Private Sub Form_Load()
    'Set the font zoom setting to the default of 100% (represented by a 1 below).
    'This means that the fonts will appear initially at the proportional size
    'set during design time. But they can be made smaller or larger at run time
    'by holding the "Shift" key and hitting the "+" or "-" key at the same time,
    'or by holding the "Ctrl" key and scrolling the mouse wheel up or down.
    fontZoom = 1

    'When the form loads, we need to find the relative position of each control
    'and save it in the control's "Tag" property so the resize event can use it
    SaveControlPositionsToTags Me
End Sub


Private Sub Form_Resize()
    'Set the height of the header and footer before calling RepositionControls
    'since it caused problems changing their heights from inside that sub.
    'The Tag property for the header and footer is set inside the SaveControlPositionsToTags sub
    Me.Section(acHeader).Height = Me.WindowHeight * CDbl(Me.Section(acHeader).Tag)
    Me.Section(acFooter).Height = Me.WindowHeight * CDbl(Me.Section(acFooter).Tag)

    'Call the RepositionControls Sub and pass this form as a parameter
    'and the fontZoom setting which was initially set when the form loaded and then
    'changed if the user holds the "Shift" key and hits the "+" or "-" key
    'or holds the "Ctrl" key and scrolls the mouse wheel up or down.
    RepositionControls Me, fontZoom
End Sub


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    'PURPOSE: Make the text on the form bigger if "Shift" and "+" are pressed
    'at the same time and smaller if "Shift" and "-" are pressed at the same time.
    'NOTE: Using the "Ctrl" key instead of the "Shift" key conflicts with Access's
    'default behavior of using "Ctrl -" to delete a record, so "Shift" is used instead

    'Was the "Shift" key being held down while the Key was pressed?
    Dim shiftKeyPressed As Boolean
    shiftKeyPressed = (Shift And acShiftMask) > 0

    'If so, check to see if the user pressed the "+" or the "-" button at the
    'same time as the "Shift" key. If so, then make the font bigger/smaller
    'by the percentage specificed in the FONT_ZOOM_PERCENT_CHANGE variable.
    If shiftKeyPressed Then

        Select Case KeyCode
            Case vbKeyAdd
                fontZoom = fontZoom + FONT_ZOOM_PERCENT_CHANGE
                RepositionControls Me, fontZoom

                'Set the KeyCode back to zero to prevent the "+" symbol from
                'showing up if a textbox or similar control has the focus
                KeyCode = 0

            Case vbKeySubtract
                fontZoom = fontZoom - FONT_ZOOM_PERCENT_CHANGE
                RepositionControls Me, fontZoom

                'Set the KeyCode back to zero to prevent the "-" symbol from
                'showing up if a textbox or similar control has the focus
                KeyCode = 0

        End Select

    End If

    'Detect if the "Ctrl" key was pressed. This variable
    'will be used later when we detect a mouse wheel scroll event.
    If (Shift And acCtrlMask) > 0 Then
        ctrlKeyIsPressed = True
    End If

End Sub

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
    'Change the ctrlKeyIsPressed variable to false when
    'any key is let up. This will make sure the form text does
    'not continue to grow/shrink when the mouse wheel is
    'scrolled after the ctrl key is pressed and let up.
    ctrlKeyIsPressed = False
End Sub


Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
    'If the "Ctrl" key is also being pressed, then zoom the form in or out
    If ctrlKeyIsPressed Then
        Debug.Print ctrlKeyIsPressed
        'The user scrolled up, so make the text larger
        If Count < 0 Then

            'Make the font bigger by the percentage specificed
            'in the FONT_ZOOM_PERCENT_CHANGE variable
            fontZoom = fontZoom + FONT_ZOOM_PERCENT_CHANGE
            RepositionControls Me, fontZoom

        'The user scrolled down, so make the text smaller
        ElseIf Count > 0 Then

            'Make the font smaller by the percentage specificed
            'in the FONT_ZOOM_PERCENT_CHANGE variable
            fontZoom = fontZoom - FONT_ZOOM_PERCENT_CHANGE
            RepositionControls Me, fontZoom
        End If

    End If
End Sub


Public Sub SaveControlPositionsToTags(frm As Form)
On Error Resume Next

    Dim ctl As Control

    Dim ctlLeft As String
    Dim ctlTop As String
    Dim ctlWidth As String
    Dim ctlHeight As String
    Dim ctlOriginalFontSize As String
    Dim ctlOriginalControlHeight As String

    For Each ctl In frm.Controls

        'Find the relative position of this control in design view
        'e.g.- This control is 5% from the left, 10% from the top, etc.
        'Those percentages can then be saved in the Tag property for this control
        'and used later in the form's resize event
        ctlLeft = CStr(Round(ctl.Left / frm.Width, 4))
        ctlTop = CStr(Round(ctl.Top / frm.Section(ctl.Section).Height, 4))
        ctlWidth = CStr(Round(ctl.Width / frm.Width, 4))
        ctlHeight = CStr(Round(ctl.Height / frm.Section(ctl.Section).Height, 4))

        'If this control has a FontSize property, then capture the
        'control's original font size and the control's original height from design-time
        'These will be used later to calculate what the font size should be when the form is resized
        Select Case ctl.ControlType
            Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                ctlOriginalFontSize = ctl.FontSize
                ctlOriginalControlHeight = ctl.Height
        End Select

        'Add all this data to the Tag property of the current control, separated by colons
        ctl.Tag = ctlLeft & ":" & ctlTop & ":" & ctlWidth & ":" & ctlHeight & ":" & ctlOriginalFontSize & ":" & ctlOriginalControlHeight

    Next

    'Set the Tag properties for the header and the footer to their proportional height
    'in relation to the height of the whole form (header + detail + footer)
    frm.Section(acHeader).Tag = CStr(Round(frm.Section(acHeader).Height / (frm.Section(acHeader).Height + frm.Section(acDetail).Height + frm.Section(acFooter).Height), 4))
    frm.Section(acFooter).Tag = CStr(Round(frm.Section(acFooter).Height / (frm.Section(acHeader).Height + frm.Section(acDetail).Height + frm.Section(acFooter).Height), 4))

End Sub


Public Sub RepositionControls(frm As Form, fontZoom As Double)
On Error Resume Next

    Dim formDetailHeight As Long
    Dim tagArray() As String

    'Since "Form.Section(acDetail).Height" usually returns the same value (unless the detail section is tiny)
    'go ahead and calculate the detail section height ourselves and store it in a variable
    formDetailHeight = frm.WindowHeight - frm.Section(acHeader).Height - frm.Section(acFooter).Height

    Dim ctl As Control

    'Loop through all the controls on the form
    For Each ctl In frm.Controls

        'An extra check to make sure the Tag property has a value
        If ctl.Tag <> "" Then

            'Split the Tag property into an array
            tagArray = Split(ctl.Tag, ":")

            If ctl.Section = acDetail Then
                'This is the Detail section of the form so use our "formDetailHeight" variable from above
                ctl.Move frm.WindowWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                                   formDetailHeight * (CDbl(tagArray(ControlTag.FromTop))), _
                                   frm.WindowWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                                   formDetailHeight * (CDbl(tagArray(ControlTag.ControlHeight)))
            Else
                ctl.Move frm.WindowWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                                   frm.Section(ctl.Section).Height * (CDbl(tagArray(ControlTag.FromTop))), _
                                   frm.WindowWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                                   frm.Section(ctl.Section).Height * (CDbl(tagArray(ControlTag.ControlHeight)))
            End If

            'Now we need to change the font sizes on the controls.
            'If this control has a FontSize property, then find the ratio of
            'the current height of the control to the form-load height of the control.
            'So if form-load height was 1000 (twips) and the current height is 500 (twips)
            'then we multiply the original font size * (500/1000), or 50%.
            'Then we multiply that by the fontZoom setting in case the user wants to
            'increase or decrease the font sizes while viewing the form.
            Select Case ctl.ControlType
                Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                    ctl.FontSize = Round(CDbl(tagArray(ControlTag.OriginalFontSize)) * CDbl(ctl.Height / tagArray(ControlTag.OriginalControlHeight))) * fontZoom
            End Select

        End If

    Next

End Sub

Here are some screenshots of what a form looks like when shrunk.

以下是表格缩小时的一些屏幕截图。

Before:

前：

After:

后：

Also, you can make the text larger by holding the Ctrlkey and scrolling the mouse wheel up (or, alternatively by holding the Shiftkey and pressing the +key.)

此外，您可以通过按住Ctrl键并向上滚动鼠标滚轮（或者，按住Shift键并按下+键）来使文本变大。

And, you can make the text smaller by holding the Ctrlkey and scrolling the mouse wheel down (or, alternatively by holding the Shiftkey and pressing the -key.)

并且，您可以通过按住Ctrl键并向下滚动鼠标滚轮（或者，按住Shift键并按下-键）来缩小文本。

Answer 2

回答by Renaud Bompuis

A few notes:

一些注意事项：

Have a look at how to anchor controls to the formso they can resize with the form.
Design your forms so they display properly on the smallest screensize that your users have.
It's important that you think about how your users will interact with your application. You cannot expect Access to magically reflow and resize everything, it's something you, as the designer of the application, need to think about.
So limit the number of controls on your form, and keep them small enough that they display correctly on whatever is the smallest reasonable screen resolution in your office.
If you do not want bars to appear, look at the form's scrollbarsproperties.
Look into the various form styles you can use: in Access 2007 and above, you can use forms in tabs. You can also make them popup, and prevent them from being resized.
Look into the following form properties and play around the various combinations to get the desired effect:

查看如何将控件锚定到表单，以便它们可以随表单调整大小。
设计您的表单，使其在您的用户拥有的最小屏幕上正确显示。
考虑用户将如何与您的应用程序交互非常重要。您不能指望 Access 神奇地重排和调整所有内容，这是您作为应用程序设计者需要考虑的事情。
因此，请限制表单上控件的数量，并保持它们足够小，以便在办公室中任何最小的合理屏幕分辨率下都能正确显示。
如果您不想显示条形，请查看表单的scrollbars属性。
查看您可以使用的各种表单样式：在 Access 2007 及更高版本中，您可以在选项卡中使用表单。您还可以使它们弹出，并防止它们被调整大小。
查看以下表单属性并尝试各种组合以获得所需的效果：

Access 2010 VBA 表单 - 自动调整表单大小

提问by Padawan

回答by

回答by Renaud Bompuis

相关推荐

最近更新

标签

Access 2010 VBA 表单 - 自动调整表单大小

提问by Padawan

回答by

回答by Renaud Bompuis

相关推荐

VBA - 运行时错误 1004

vba Word 文档：设置为横向

vba 如何在 MS Access 中引用子表单

excel vba 如何将多个非连续范围的值复制到数组中

相关推荐

最近更新

标签