Excel Macros 简明教程

Excel Macros - UserForms

有时，您可能需要反复收集他人的信息。Excel VBA 为您提供了一种处理此任务的简单方法 - UserForm 。与您填写的任何其他表单一样，用户窗体让您可以轻松了解要提供哪些信息。用户窗体非常友好，因为它提供的控件不言自明，必要时还附有其他说明。

At times, you might have to collect information repeatedly from others. Excel VBA provides you with an easy way of handling this task- UserForm. As any other form that you fill up, UserForm makes it simple to understand, what information is to be provided. UserForm is user friendly in the way that the controls provided are self-explanatory, accompanied by additional instructions where necessary.

用户窗体的主要优点是，您可以节省您花在信息填充方式和内容上的时间。

Major advantage of UserForm is that you can save on time that you spend on what and how the information is to be filled.

Creating a UserForm

要创建用户窗体，请按照以下步骤进行 −

To create a UserForm, proceed as follows −

  1. Click the DEVELOPER tab on the Ribbon.

  2. Click Visual Basic. A Visual Basic window for the workbook opens.

  3. Click Insert,

  4. Select UserForm from the dropdown list.

creating userform

用户窗体出现在窗口右侧。

The UserForm appears on the right side of the window.

userform appears

Understanding the UserForm

最大化 UserForm.xlsx – UserForm1 窗口。

Maximize the UserForm.xlsx – UserForm1 window.

你现在处于设计模式。您可以在用户窗体上插入控件，并为相应的操作编写代码。控件在工具箱中提供。用户窗体的属性在属性窗口中。UserForm1（用户窗体的标题）在项目资源管理器中的“窗体”下给出。

You are in the design mode now. You can insert controls on the UserForm and write code for the respective actions. The controls are available in the ToolBox. Properties of UserForm are in the Properties window. UserForm1 (caption of the UserForm) is given under Forms in the Projects Explorer.

understanding userform

  1. Change the caption of the UserForm to Project Report – Daily in the properties window.

  2. Change the name of the UserForm to ProjectReport.

projectreport

这些更改反映在用户窗体、属性和项目资源管理器中。

The changes are reflected in the UserForm, properties and project explorer.

Controls in the ToolBox

用户窗体将具有不同的组件。当您单击任何组件时，系统会指示您提供什么信息以及如何提供信息，或者您会看到可供选择的选项。所有这些都是通过用户窗体的工具箱中的 ActiveX 控件提供的。

A UserForm will have different components. As and when you click on any of the components, either you will be provided with instructions on what and how the information is to be provided or you will be provided with options (choices) to select from. All these are provided by means of ActiveX controls in the ToolBox of the UserForm.

Excel 提供两种类型的控件——窗体控件和 ActiveX 控件。您需要了解这两种类型的控件之间的区别。

Excel provides two types of controls – Form controls and ActiveX controls. You need to understand the difference between these two types of controls.

Form controls

窗体控件是与较早版本的 Excel（从 Excel 5.0 版本开始）兼容的 Excel 原始控件。窗体控件还设计为在 XLM 宏表上使用。

Form controls are the Excel original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.

您可以使用窗体控件运行宏。您可以向控件分配现有宏，或编写或记录新宏。单击控件时，宏将运行。您已了解如何从窗体控件中插入命令按钮到工作表中来运行宏。然而，这些控件无法添加到用户窗体中。

You can run macros by using Form controls. You can assign an existing macro to a control, or write or record a new macro. When the control is clicked, the macro. You have already learnt how to insert a command button from Form controls in the worksheet to run a macro. However, these controls cannot be added to a UserForm.

ActiveX controls

ActiveX 控件可用于 VBA 用户窗体上。ActiveX 控件具有丰富的属性，您可以使用这些属性来自定义它们的外观、行为、字体和其他特性。

ActiveX controls can be used on VBA UserForms. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts and other characteristics.

您在用户窗体工具箱中拥有以下 ActiveX 控件 −

You have the following ActiveX controls in the UserForm ToolBox −

  1. Pointer

  2. Label

  3. TextBox

  4. ComboBox

  5. ListBox

  6. CheckBox

  7. OptionButton

  8. Frame

  9. ToggleButton

  10. CommandButton

  11. TabStrip

  12. MultiPage

  13. ScrollBar

  14. SpinButton

  15. Image

除了这些控件外，Visual Basic 还为您提供了 MsgBox 函数，该函数可用于显示消息和/或提示用户采取操作。

In addition to these controls, Visual Basic provides you with MsgBox function that can be used to display messages and/or prompt the user for an action.

在接下来的几节中，您将了解这些控件和 MsgBox。然后，您将能够选择需要哪些控件来设计您的用户窗体。

In the next few sections, you will understand these controls and MsgBox. Then, you will be in a position to choose which of these controls are required to design your UserForm.

Label

您可以通过显示描述性文本（例如标题、说明和/或简要说明）来使用标签进行标识目的。

You can use Labels for identification purpose by displaying descriptive text, such as titles, captions and / or brief instructions.

Example

label

TextBox

您可以使用文本框（一个矩形框）来键入、查看或编辑文本。您还可以将文本框用作呈现只读信息的静态文本字段。

You can use a TextBox that is a rectangular box, to type, view or edit text. You can also use a TextBox as a static text field that presents read-only information.

Example

textbox

List Box

可以使用列表框来显示一个或多个用户可以选择文本项目列表。使用列表框显示数量或内容不同的众多选择。

You can use a List Box to display a list of one or more items of text from which a user can choose. Use a list box for displaying large numbers of choices that vary in number or content.

  1. Insert a ListBox on the UserForm.

  2. Click on the ListBox.

  3. Type ProjectCodes for Name in the Properties window of the ListBox.

列表框有三种类型 −

There are three types of List Boxes −

  1. Single-selection List box − A single-selection List Box enables only one choice. In this case, a list box resembles a group of option buttons, except that a list box can handle a large number of items more efficiently.

  2. Multiple selection List Box − A multiple selection List Box enables either one choice or contiguous (adjacent) choices.

  3. Extended-selection List Box − An extended-selection List Box enables one choice, contiguous choices and noncontiguous (or disjointed) choices.

可以在属性窗口中选择这些列表框类型之一。

You can select one of these types of List Boxes, from the Properties window.

listbox

  1. Right click on the UserForm.

  2. Select View Code from the dropdown list. The code window of UserForm opens.

  3. Click Initialize in the top right box of the code window.

  4. Type the following under Private Sub UserForm_Initialize().

ProjectCodes.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")
initialize

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

select run

接下来，你可以编写在列表中选择某项后执行操作的代码。否则，你可以仅仅显示选中的文本，例如在报表中填写项目代码的情况亦是如此。

Next, you can write code for actions on selecting an item in the list. Otherwise, you can just display the text that is selected, which is the case for filling the Project Code in the Report.

ComboBox

你可以使用 ComboBox ，它将文本框与列表框组合起来以创建下拉列表框。与列表框相比，组合框更紧凑，但要求用户单击向下箭头以显示项列表。使用组合框从列表中仅选择一项。

You can use ComboBox that combines a text box with a list box to create a dropdown list box. A combo box is more compact than a list box but requires the user to click the down arrow to display the list of items. Use a combo box to choose only one item from the list.

  1. Insert a ComboBox on the UserForm.

  2. Click the ComboBox.

  3. Type ProjectCodes2 for Name in the Properties window of the ComboBox.

combobox

  1. Right click on the UserForm.

  2. Select View Code from the dropdown list.

  3. The code window of UserForm opens.

按下方所示键入以下内容。

Type the following as shown below.

ProjectCodes2.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")
code window

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

run tab

单击向下箭头显示项目列表。

Click the down arrow to display the list of items.

click down arrow

单击所需的项目，例如 Project2016-5。选择框中将显示已选项。

Click on the required item, say, Project2016-5. The selected option will be displayed in the combo box.

required item

CheckBox

您可以使用复选框选中单击框中显示的一个或多个选项。这些选项会有标签，您可以清晰地看到已选选项。

You can use check boxes to select one or more options that are displayed by clicking in the boxes. The options will have labels and you can clearly visualize what options are selected.

复选框可以有两种状态 −

A check box can have two states −

  1. Selected (turned on), denoted by a tick mark in the box

  2. Cleared (turned off), denoted by a clear box

在组合框中使用复选框选择选项可以节省空间。在此情况下，复选框还可以有第三个状态 −

You can use check boxes for selection of options in a combo box to save space. In such a case, the check box can have a third state also −

  1. Mixed, meaning a combination of on and off states, denoted by a black dot in the box. This will be displayed to indicate multiple selections in the combo box with check boxes.

  2. Insert check boxes in the UserForm as shown below.

checkbox

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

  3. Click in the boxes for your selected options.

boxes

OptionButton

您可以使用选项按钮（也称为单选按钮），在有限的互斥选项集中进行单选。选项按钮通常包含在组框或框架中。

You can use an option button, also known as the radio button to make a single choice within a limited set of mutually exclusive choices. An option button is usually contained in a group box or a frame.

选项按钮用一个小圆圈表示。选项按钮可以有以下两种状态之一 −

An option button is represented by a small circle. An option button can have one of the following two states −

  1. Selected (turned on), denoted by a dot in the circle

  2. Cleared (turned off), denoted by a blank

Frame

您可以使用框架控件（也称为组框），将相关控件组合到一个可视化单元中。通常，选项按钮、复选框或密切相关的控件会被分组到一个框架控件中。

You can use a frame control, also referred to as a group box to group related controls into one visual unit. Typically, option buttons, check boxes or closely related contents are grouped in a frame control.

框架控件由一个带有可选标签的矩形对象表示。

A frame control is represented by a rectangular object with an optional label.

  1. Insert a frame with caption “Choice”.

  2. Insert two option buttons with captions “Yes” and “No” in the frame control. The options Yes and No are mutually exclusive.

frame

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

  3. Click on your selected option.

selected option

ToggleButton

你可以使用一个切换按钮指示一个状态，比如是或否，或一个模式，比如开或关。这个按钮在被点击时会在启用和禁用状态之间切换。

You can use a toggle button to indicate a state, such as Yes or No, or a mode, such as on or off. The button alternates between an enabled and a disabled state when it is clicked.

如下图所示在 UserForm 中插入一个切换按钮 −

Insert a toggle button on UserForm as shown below −

togglebutton

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list. The toggle button will be in enabled state by default.

default

点击切换按钮。切换按钮将被禁用。

Click the toggle button. The toggle button will be disabled.

toggle button

如果你再次点击切换按钮，它将被启用。

If you click the toggle button again, it will be enabled.

CommandButton

你可以使用一个命令按钮运行一个宏，当用户点击它时执行一些操作。你已经学会如何在工作表中使用一个命令按钮来运行一个宏。

You can use a command button to run a macro that performs some actions when the user clicks on it. You have already learnt how to use a command button on a worksheet to run a macro.

命令按钮也被称为推送按钮。如下图所示在 UserForm 中插入一个命令按钮 −

Command button is also referred to as a push button. Insert a command button on the UserForm as shown below −

commandbutton

  1. Right click on the command button.

  2. Type the following code in the sub Commandbutton1_click ().

ProjectCodes2.DropDown
commandbutton1

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

daily report

点击命令按钮。组合框的下拉列表打开，因为这是你在代码中编写的操作。

Click the command button. The dropdown list of combo box opens, as it is the action that you have written in the code.

combo box

TabStrip

你可以在 UserForm 中插入一个类似于 Excel 标签页的标签页控件。

You can insert a tab strip that resembles Excel tabs on the UserForm.

ScrollBar

你可以使用滚动条通过点击滚动箭头或拖动滚动框来浏览一系列的值。

You can use a scroll bar to scroll through a range of values by clicking on the scroll arrows or by dragging the scroll box.

通过在所需位置绘制并调整滚动条的长度，在 UserForm 中插入一个滚动条。

Insert a scroll bar on the UserForm by drawing it at the required position and adjust the length of the scroll bar.

scrollbar

  1. Right click on the scroll bar.

  2. Select View Code from the dropdown list. The Code window opens.

  3. Add the following line under sub ScrollBar1_Scroll().

TextBox2.Text = "Scrolling Values"
scrolling value

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

scrollbar report

拖动滚动框。文本 - 滚动值将显示在文本框中，就像你指定为滚动条滚动操作一样。

Drag the scroll box. The Text – Scrolling Values will be displayed in the text box as you specified it as the action for scroll bar scroll.

text box

MsgBox ()

你可以在点击某物时使用 MsgBox() 函数显示一条消息。这可以是一条指导或一些信息、一个警告或一个错误警报。

You can use the MsgBox () function to display a message when you click on something. It can be a guideline or some information or a warning or an error alert.

例如，当开始滚动滚动框时，你可以显示一条消息来提示值正在滚动。

For example, you can display a message that values are being scrolled when you start scrolling the scroll box.

msgbox function

Message Box Icon Displays

你可以使用消息框图标显示来描绘具体的消息。你有各种消息框图标，以满足你的目的 −

You can use message-box icon displays that portray the specific message. You have the multiple message box icons to suit your purpose −

  1. Type the following code under ScrollBar1_scroll.

MsgBox "Select Ok or Cancel", vbOKCancel, "OK  - Cancel Message"
MsgBox "It's an Error!", vbCritical, "Run time result"
MsgBox "Why this value", vbQuestion, "Run time result"
MsgBox "Value Been for a Long Time", vbInformation, "Run time result"
MsgBox "Oh Is it so", vbExclamation, "Run time result"

  1. Click the Run tab on the Ribbon.

  2. Select Run Sub/UserForm from the dropdown list.

  3. Drag the scroll box.

你将依次收到以下消息框。

You will get the following message boxes successively.

message boxes

Designing UserForm

现在，你已了解可以在 UserForm 上使用不同的控件。选择控件，在需要时对它们进行分组，并按照一定的合理顺序排列在 UserForm 上。将需要的操作作为代码编写，对应于各个控件。

Now, you have an understanding of the different controls that you can use on a UserForm. Select the controls, group them if required and arrange them on the UserForm as per some meaningful sequence. Write the required actions as code corresponding to the respective controls.

有关 UserForm 的示例，请参阅本教程库中的 VBA 教程。

Refer to the VBA tutorial in this tutorials library for an example of UserForm.