{"id":46,"date":"2014-01-24T18:19:33","date_gmt":"2014-01-25T01:19:33","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=46"},"modified":"2016-10-06T18:15:13","modified_gmt":"2016-10-07T01:15:13","slug":"interface","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/vba\/interface\/","title":{"rendered":"User Forms"},"content":{"rendered":"

A form is an interface with a standard structure and format that makes it easier to capture, organize, and edit information (here is an overview of forms<\/a>). You can create forms on spreadsheets or on VBA user forms, by using controls\u00a0. But before creating a customized user form, consider using built-in dialog boxes available from Excel.<\/p>\n

1. Excel built-in dialogs<\/h2>\n

Message boxes<\/strong>: allows you to print something for the user to see in a small dialog box with an OK button.<\/p>\n

MsgBox <\/span>(Prompt<\/span>, [Buttons<\/span>], [Title<\/span>])
\nMsgBox <\/span>(“Task completed”)
\nMsgBox <\/span>(“The value of the output is ” & ValueVar)<\/p>\n

Note: [Buttons] and [Title] are optional arguments.<\/p>\n

You can set the type of buttons displayed with the Button argument<\/p>\n

0 = OK button only (default)
\n1 = OK and Cancel buttons.
\n3 = Yes, No, and Cancel buttons.<\/p>\n

MsgBox returns an integer indicating which button the user clicked:<\/p>\n

1 = OK
\n2 = Cancel
\n6 = Yes
\n7 = No<\/p>\n

For more information about MsgBox go to MsgBox Function<\/a>.<\/p>\n

Input box<\/strong>: allows you to prompt the user to enter some value in \u000ba small dialog box and returns a string containing the contents of the text box<\/p>\n

x = InputBox<\/span> (Prompt<\/span>, [Title<\/span>], [DefaultResponse<\/span>])
\nx = InputBox<\/span> (“Insert a number”, ,10)<\/p>\n

If the user clicks Cancel, a zero-length string is returned<\/p>\n

Input box method<\/strong>: displays a dialog box to input more specific type of data (formula, number, text, logical value or range of cells)<\/p>\n

x = Application.InputBox <\/span>(prompt<\/span>:=”Insert a formula”, type<\/span> = 0)
\nSet<\/span> r = Application.InputBox <\/span>(prompt<\/span>:=”Select a range”, type<\/span>\u00a0:= 8)<\/p>\n

\u00a0where Type specifies the return data type<\/p>\n

0 = Formula
\n1 =\u00a0 Number
\n2 = Text (String)
\n4 = Logical value (True or False)
\n8 = Cell reference (Range object)
\n16 = Error value
\n64 = Array of values<\/p>\n

For more information about the input box method go to Application.InputBox Method<\/a>.<\/p>\n

Other built-in dialog boxes are\u00a0GetOpenFilename<\/a> method and\u00a0GetSaveAsFilename<\/a> method.\u00a0 The Dialogs object of the Application object contains all the built-in Excel dialog boxes.<\/p>\n

2. Controls<\/h2>\n

Note: In this section we focus on ActiveX Controls, which provide more interaction with VBA macros. You can find more about ActiveX Controls here<\/a>.<\/p>\n

Controls are objects that display data or make it easier for users to enter or edit data, perform an action, or make a selection. The Control Toolbox<\/span> in VBE contains a set of controls, which can be placed directly on the spreadsheet or in user forms.<\/p>\n

 <\/p>\n

\"Control<\/a><\/p>\n

Each control has a set of properties<\/span> which can be \u000bmodified and a set of events<\/span> triggered by some action. This website<\/a> provides a list of properties and events for the most common controls. Here is a summary:<\/p>\n\n\n\n\n\n\n\n\n\n\n
\n

Control<\/p>\n<\/td>\n

\n

Properties<\/p>\n<\/td>\n

\n

Events<\/p>\n<\/td>\n<\/tr>\n

\n

Label<\/p>\n<\/td>\n

\n

Font: the font of text to be displayed on the label<\/p>\n

Caption: the text of the label<\/p>\n<\/td>\n

<\/td>\n<\/tr>\n
\n

Text Box<\/span><\/p>\n<\/td>\n

\n

Text: the text of the textbox<\/p>\n

Multiline: If true, the textbox can accept multiple lines of text.<\/p>\n<\/td>\n

\n

Keypress<\/span>: triggered when the keyboard is pressed.<\/p>\n

Change: \u00a0<\/span>triggered when the
\ntext of the textbox is changed.<\/p>\n<\/td>\n<\/tr>\n

\n

Button<\/p>\n<\/td>\n

\n

Caption: text displayed on the button<\/p>\n<\/td>\n

\n

Click: triggered when the button is clicked.<\/p>\n<\/td>\n<\/tr>\n

\n

Combo Box<\/span><\/p>\n<\/td>\n

\n

Value: displayed value of the combo box<\/p>\n

List: contains all items of the combo box<\/p>\n<\/td>\n

\n

Change: triggered when an item of the combo box is selected.<\/p>\n

AddItem: adds an item<\/p>\n<\/td>\n<\/tr>\n

\n

Check Box<\/span><\/p>\n<\/td>\n

\n

Caption: text displayed on the right of the checkbox<\/p>\n

Value: True when the checkbox is checked, otherwise it is unchecked.<\/p>\n<\/td>\n

\n

Change: triggered when the checkbox is checked or unchecked.<\/p>\n<\/td>\n<\/tr>\n

\n

List Box<\/span><\/p>\n<\/td>\n

\n

Value: value of the selected item<\/p>\n

List: contains all items of the list box<\/p>\n<\/td>\n

\n

Change: triggered when an item of the listbox <\/span>is selected.<\/p>\n

AddItem: adds an item to the list<\/p>\n<\/td>\n<\/tr>\n

\n

Option Button<\/span><\/p>\n<\/td>\n

\n

Caption: text displayed on the right of the option button<\/p>\n

Value: True when the option is selected, otherwise it\u2019s not selected.<\/p>\n<\/td>\n

\n

Click: triggered when the option is clicked<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Event Procedures<\/span> connect actions of controls to a specific macro of VBA code. You can find the complete list of event procedures for a control at the top of the Code Window. Select an object (left hand list) and then the event (right hand list). You can also set the control properties in the properties window<\/span>.<\/p>\n

3. VBA User Forms<\/h2>\n

VBA User Forms are custom dialog boxes built with controls and their attached macros. To create a form follow these steps:<\/p>\n

1. Insert a UserForm into your VBAProject<\/strong>: on VBE, go to Insert\u00a0\u2192 User Form \u000b from the menu.<\/p>\n

2. Write a procedure to display the UserForm<\/strong>: you can display the form when you click a button or open a workbook. Use the UserForm1<\/span>.Show<\/span> statement.<\/p>\n

3. Set up your form properties<\/strong>: on the VBE properties window set the form properties such as Name, Caption, BorderStyle and ScrollBars.<\/p>\n

4. Add controls from the Control Toolbox<\/strong><\/p>\n

5. Modify properties for the controls<\/strong>: they can be changed when designing the form (using VBE’s Properties window), or by writing code that manipulate these characteristics at run time.<\/p>\n

6. Write event-handler procedures for the controls<\/strong>: event procedures are written in a separate user form module.<\/p>\n

Typically you will need to define a procedure to initialize your controls when your form is displayed. You can do this with the Initialize<\/span> event of the user form: UserForm1_Initialize(). In this procedure you can set your combo box or list box values and initialize your form variables.<\/p>\n

At some point you will need to close or hide your form. Use the statements UserForm1.Hide<\/span> in any procedure or Unload Me<\/span> within some of the controls’ event procedures.<\/p>\n

Here<\/a>‘s a detailed walkthrough of creating a form.<\/p>\n","protected":false},"excerpt":{"rendered":"

A form is an interface with a standard structure and format that makes it easier to capture, organize, and edit information (here is an overview of forms). You can create forms on spreadsheets or on VBA user forms, by using controls\u00a0. But before creating a customized user form, consider using built-in dialog boxes available from […]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":2,"menu_order":8,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-46","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/46","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=46"}],"version-history":[{"count":29,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/46\/revisions"}],"predecessor-version":[{"id":1511,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/46\/revisions\/1511"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/2"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}