Forms

Forms are graphic objects that are created by the designer, and they consist of a variety of controls for easier data entry. These may be: text input field, radio buttons, check boxes, drop-down lists, combined lists, etc. By adding control to the mask, prerequisites for retrieving values into a document ​​are created, and after the scheduled action they are imported on specific location within a worksheet.

After opening the VBA editor, we can add a new form from the Insert menu. Once done, it will appear in the working part of the window and at the same time in the list of objects in the Forms folder under the name UserForm <n>. At the same time, Toolbox appears, which contains the selection of controls that can be added into the form. If we accidentally switch it off, we can again turn it on from the View menu. Now we just have to make one simple form, for example, one that contains information about a single person.

We’ll add several controls to the form. Controls known as Labels are used to enter plain text labels, while the Text Box is used to enter text that could be further transferred into document. Option Button controls are used to enter radio buttons, and at the end of Buttons they are used to add button controls to the form. Each of the controls has a number of properties that can affect its appearance. It can be a background color, text color, type, size, and font style, Caption, etc.

When we have completed the design, should follow the writing of methods that are triggered if some of the values ​​on the controls change. They are optional, except in the case of a method-subroutine that specifies how, after confirmation of the entry, the data is entered into the worksheet. If we have added the OK button, by double-clicking it we open the editor where should enter the method by which we determine what happens after the confirmation of the entry. In this example, we will transfer the values ​​of the text fields from the form to the appropriate places in the worksheet:

Private Sub CommandButton1_Click()

Worksheets(1).Cells(1, 1) = VBAProject.UserForm1.TextBox1
Worksheets(1).Cells(2, 1) = VBAProject.UserForm1.TextBox2

End Sub

To show the form in the first place should add, in a separate module, a subroutine that contains minimum one command:

UserForm1.Show

If within the form there are lists or other objects whose values should be initialized before displaying the form we will write the appropriate code. In the end, we just need to add a rectangle shape and assign it a macro. By clicking the button we will open the form in which we enter the data, and after we confirm the entry they will be transferred to the appropriate place in the worksheet.