How to add a User. Form to aid data entry in Excel. When you need a user- friendly data input process, create an Excel User. Form. Users who are unfamiliar with Excel will find these forms much easier to navigate than a sheet. Besides ease of use, they're great tools for restricting choices. In this article, I'll show you how to create a User. Form object, add controls to it, and then use Visual Basic for Applications (VBA) to link the controls to a sheet. You can work with your own data or download the example . About the data. A User. Form guides data entry, so the first thing you need is a data range. Figure A shows the simple data set we'll work with. Below we will look at a program in Excel VBA which creates a Userform that contains multiple pages. This userform also contains images. Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and. This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows. We are now going to create the Sub UserForm. When you use the Show method for the Userform, this sub will automatically be. The workbook must be macro- enabled if you're using version 2. Click the File tab (or Office button) and choose Save As. Choose a location as necessary. From the Save As Type drop- down menu, choose Excel Macro- Enabled Workbook (*. Figure A We'll create a simple User. Form that will help users enter new records into this data set. Create the User. Form.
With a bit of data in a worksheet, you're ready to move to the Visual Basic Editor (VBE) to create the User. Form: Press . When you do, the VBE updates the property dialog's title, accordingly (Figure C). Creating VBA Add-ins to Extend and Automate Office Documents Assembling Documents on SharePoint 2010 Sites by Merging. As String, oDoc As Word.Document Sub DocVarbs() 'GetSetting and SaveSetting use VB and VBA Program Settings in the and. Introduction to Excel VBA Programming - Source Codes here - http:// - More tips. Summary: Introduces Visual Basic for Applications (VBA) in Excel 2010 to the Excel power user who is not yet a programmer. Developer tab, click the Visual Basic button to open the Visual Basic Editor, and then go to the Insert menu and choose UserForm. Figure C Add controls. The blank User. Form is ready for input controls. We'll add a few text box, combo box, and command button controls. To add the first text box, do the following: With the User. Form selected, click Text Box in the Toolbox and then drop it onto the form. If the Toolbox isn't visible, be sure to click the User. Form. It's visible when the User. Form is selected. With the new text box control selected, name it txt. Given. Name using the property sheet, which will update for the selected element (Figure D). Figure D Using Figure E as a guide, add the additional controls listed in Table A and name them. The labels aren't necessary on this simple example, but when creating a User. Form for your own data, you'll probably want to include them. When you do, Excel's default names are usually adequate. Enter a descriptive caption for each label and the two command buttons. It's a good idea to save your work as you progress. Figure E Add the remaining controls. Table AAdd code. The code behind the form makes it work. Don't let the amount of code intimidate you. The Save command button does most of the work. Once you enter all the values, the code in this button will transfer the entered values to the sheet. To add code, double- click the User. Form to open its module and enter the procedures in Listing A. Instead, download one of the example files: Module. Animals. frm.) Listing APrivate Sub cbo. Class. The fourth procedure, cmd. Add. This way, the user can enter multiple records quickly. The last procedure, cmd. Close. The procedure in Listing B opens the User. Form. In the next section, we'll add a macro button that calls this procedure to the Quick Access Toolbar (QAT). Don't add this procedure to the User. Form's module. Instead, choose Module from the Insert menu and add the short procedure. Listing BSub Show. Animals. UF(). 'Display Animals User. Form. To add the button, do the following: Click the QAT drop- down and choose More Commands. From the Choose Commands From drop- down, choose Macro. Select Show. Animals. UF (Figure F) and click Add. Figure F Click Close. Use the User. Form. To use the User. Form, click the macro button on the QAT to display it — wasn't that easy? Use the drop- down lists to choose values for the combo box controls and enter values for the text box controls. Figure G shows a new record in the User. Form. Figure G Add a new record. To copy those values to the worksheet, click the Save Animal button. The code behind the User. Form will copy the values from the User. Form controls to the sheet and clear the controls for the next record, as shown in Figure H. To close the form, click the Close button. Figure H Copy the values to the sheet. Worth noting. None of the code saves the record once it's copied to the sheet. You must remember to save the workbook to save new records at the sheet level. You can add appropriate code to the User. Form or allow users to do that themselves. In addition, there's no error- handling or data validation in this bare- bones code. Using a combo box or list box helps you limit choices, but you'll need additional code to protect the validity of text and number values and to force users to enter values for required fields. As you can see, the example form allows the user to leave fields empty. Send me your question about Office. I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. I'm not reimbursed by Tech. Republic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail. Creating VBA Add- ins to Extend and Automate Microsoft Office Documents. As mentioned earlier, the custom Help example here is among the simplest uses for an add- in. You can use VBA to create add- ins that provide complete document solutions for many types of requirements. The tasks in the sections that follow are just a beginning. The intent is to help you get started with concepts that you can apply to many different types of needs, including gathering and storing user information, populating documents, evaluating document content, and interacting with built- in tasks such as closing a file or starting the application. As you explore these concepts, remember that most of the underlying VBA shown can apply to Word, Power. Point, or Excel regardless of which application the specific example uses. Automating Document Creation Tasks. When automating content for users, you might need to collect and store user information and preferences, and then use that information to generate or customize content programatically. In this example, a Word add- in collects contact information and paper size preference. The add- in also gives the user the ability to generate a company- branded letter document. When creating a letter, the add- in automatically uses the saved information. This sample add- in contains one User. Form and three modules: The User. Form is for collecting and storing the user information and preferences. One module is for the variables required by multiple procedures, such as retrieving the stored user information. Another module is for the ribbon controls. The last module is for the code to generate custom documents. For this task, create a dialog box (User. Form) where the user can enter the information. Then, save that information to the Windows Registry. Note. Before you begin the following procedure, you must create the VBA project. Also note that, for Office for Mac 2. Windows Registry. On Mac OS, these methods work the same and save information to a . To collect and save user information. In the Visual Basic Editor, create and populate the User. Form with the controls that you will need. To do so, on the Insert menu, click User. Form. If the Toolbox (which contains the available User. Form controls) doesn. You can specify many aspects of control appearance and behavior from this window. Figure 2 shows the completed User. Form object for this example. Note the usability details that make user interaction with the User. Form easy. For example, accelerator keys are provided for keyboard users, controls are consistently sized and aligned, and frames are used to visually organize controls. In addition to the features that you can see, the tab order is set for ease of use by keyboard users. To set tab order for controls in a User. Form, right- click the form and then click Tab Order. Then, right- click in each frame and click Tab Order to reorder the controls that appear in the selected frame. Figure 2. User. Form after coding is complete. Create a module for the variables that you will need for the User. Form, including variables to retrieve the settings that the User. Form code will save and to populate controls in the User. Form. Option. Explicit. Public o. Name As. String, o. Title As. String, o. Add. 1 As. String, o. Add. 2 As. String, . The cmd. Save procedure saves the information provided by the user to the Registry. Option. Explicit. Private. Sub cmd. Cancel. In that module, add a procedure to display the User. Form and declare the procedure as a Ribbon control. Sub User. Info(By. Val control As IRibbon. Control). End. Sub. Notice that this example saves the user information to the Registry. That same saved information can then be accessed by other add- ins if needed, such as when creating custom content for Power. Point or Excel. In fact, the code and User. Form from this example could have been created identically in Power. Point or Excel. To create a custom document. For this task, start with a document template such as the company- branded letter in this example. The code for this example generates a document from the template. It then customizes the document based upon user preference and populates stored user information into content controls that appear in the document body and footer. Notice, in Figure 3, that this letter- size document template includes two background shapes (for the gray gradient backing and for the red bar) that will need to be adjusted programmatically if the user. Those shapes have been named for ease of identification in the code using the Selection pane in Word 2. To access the Selection pane, look on the Home tab, and then in the Editing group, click Select. In Figure 3, the name and title in the document body and the address, phone, and email (as well as the phone and email labels) in the footer are in content controls. You can insert content controls from the Developer tab, in the Controls group. In that same group, click Design Mode to access the placeholder text for editing. Click Properties to customize control settings, such as to add the tags that are used to identify content controls in the code that follows. Note. In Word for Mac 2. Instead, consider using table cells or bookmarks as reference points for populating document content as shown in this section. To add a bookmark so that it. Note that you can only do this programmatically. To do so, click in the document where you want to add the bookmark and then, in the Visual Basic Editor, use the Immediate Window to add the bookmark. The code for adding a bookmark at the selection is shown in the following code sample. Selection. Range. Bookmarks. Add(. Template that the code uses to generate a document. The code to generate and customize this document follows. It is a good idea to create a separate module for this code because it is likely to be one of several document types the user could create from a real world version of an add- in such as this. Once this code is complete, add a procedure in the ribbon module created earlier that you can use to declare a ribbon control for calling this macro. Sub New. Letter(). On. Error. Go. To Error. Handler. Dim cc As Content. Control. 'If the user has not yet saved their information, prompt with that option. If Get. Setting(. The template may be damaged. Depending upon requirements, such as if the same user information needed to appear in multiple locations in the document or if information appears on a cover page that might be swapped with another cover page building block, you might prefer to use bound controls. Creating the UI for the Add- in. The add- in UI in this example uses a split button control placed on the Home tab, following the Paragraph group, as shown in Figure 4. To create this Ribbon customization, follow steps 1 through 4 in the preceding custom. UI example. For step 2, use the markup that follows. Add- in customization on the Home tab. This example uses content controls for populating the saved information. But you can take similar action to populate other objects in a Word document or in a Power. Point presentation or Excel workbook. For example, populate content in a table cell or at a bookmarked location in a Word document; in a text box in Power. Point; or in a worksheet cell in Excel. Note. Application events, discussed in the following section, are not widely supported in Office for Mac 2. Power. Point 2. 01. However, you can use the same steps for setting up the module and class module in Excel for Mac 2. See the Note that follows the procedure in the next section for help getting started with application events in Word or Excel. Also note that document- level events are supported in Word 2. Power. Point 2. 01. Interacting with Application Events. There might be many reasons why you want your add- in to interact with built- in actions that occur in the application or in a particular document, such as when the user prints or closes a file. VBA gives you a few ways to interact with events, including those that occur at the application- level (that is, regardless of which document is open) and those that occur at the document- level (that is, only apply to a specific document, such as those based on a particular template). Document- level events are more commonly included in VBA for an individual document or template. And you might create code in your add- in to interact with the behavior that is built in to the document or template. But, application- level events are often more convenient for add- ins because they can be built directly into your Word, Power. Point, or Excel add- in to interact with actions that the user takes. This example examines how to create an application- level event in Power. Point to check company presentations when the user closes the file. The code checks for multiple masters in the presentation and gives the user the option to programmatically clean- up the masters and leave only the original, properly- branded master in the file. Note. The article uses this example because it helps to address a common user complaint, particularly in companies migrating from a version of Microsoft Office that uses the legacy file formats (Office 2. Office 2. 01. 0 (or Office 2. When users copy slides from presentations that were created in an earlier version, they often use the Keep Source Formatting command because it appears to make the slide look like the others in the presentation. But, that command actually adds another master and set of layouts every time that it is used. The results are presentations with an unwieldy number of masters, inconsistent formatting, and bloated file size that users and tech support staff interpret as damaged or corrupted files. A tool that is similar to the example code can be used to help the user before presentations become difficult to manage and to educate the user about the ramifications of specific actions. To create an application event, you must add some code to any existing module in your VBA project, and add a class module for the event code itself. The following example also uses a User.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2017
Categories |