Determine how the dialog box will be used and at what point it will be displayed in your VBA macro.
2. Press Alt+F11 to activate the VBE and insert a new UserForm object.
A UserForm object holds a single UserForm.
Add controls to the UserForm.
Controls include items such as text boxes, buttons, check boxes, and list boxes.
Use the Properties window to modify the properties for the controls or for the UserForm itself.
Write event-handler procedures for the controls (for example, a macro that executes when the user clicks a button in the dialog box).
These procedures are stored in the Code window for the UserForm object.
Write a procedure (stored in a VBA module) that displays the dialog box to the user.
Don’t worry if some of these steps seem foreign. I provide more details in the following sections, along with step-by-step instructions for creating a UserForm.
When you are designing a UserForm, you are creating what developers call the Graphical User Interface (GUI) to your application. GUI also stands for Gaming Under the Influence, but that’s not relevant here.
Take some time to consider what your form should look like and how your users are likely to want to interact with the elements on the UserForm. Try to guide them through the steps they need to take on the form by carefully considering the arrangement and wording of the controls. Like most things VBA-related, the more you do it, the easier it gets.
Working with UserForms
Each dialog box that you create is stored in its own UserForm object — one dialog box per UserForm. You create and access these UserForms in the Visual Basic Editor.
Inserting a new UserForm
Insert a UserForm object with the following steps:
1. Activate the VBE by pressing Alt+F11.
Select the workbook in the Project window.
3. Choose Insert➪UserForm.
The VBE inserts a new UserForm object, which contains an empty dialog box.
Figure 16-2 shows a UserForm — an empty dialog box. Your job, if you choose to accept it, is to add some controls to this UserForm.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
|
|
|
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture № 1 2 . UserForm Techniques and Tricks
In This Chapter
1. Using a custom dialog box in your application
2.Creating a dialog box: a hands-on example
| |
he previous chapters show you how to insert a UserForm (which contains a custom dialog box), add controls to the UserForm, and adjust some
of the control’s properties. These skills, however, won’t do you much good unless you understand how to make use of UserForms in your VBA code. This chapter provides these missing details and presents some useful techniques and tricks in the process.
Using Dialog Boxes
When you use a custom dialog box in your application, you normally write VBA code that does the following:
✓ Initializes the UserForm controls. For example, you may write code that sets the default values for the controls.
✓ Displays the dialog box by using the UserForm object’s Show method.
✓ Responds to the events that occur for the various controls — such as clicking a CommandButton.
✓ Validates the information provided by the user (if the user did not cancel the dialog box). This step is not always necessary.
✓ Takes some action with the information provided by the user (if the information is valid).
A UserForm Example
This example demonstrates the five points I describe in the preceding section. In this example, you use a dialog box to get two pieces of information: a person’s name and sex. The dialog box uses a TextBox control to get the name, and it uses three OptionButtons to get the sex (Male, Female, or Unknown). The information collected in the dialog box is then sent to the next blank row in a worksheet.
Creating the dialog box
Figure 18-1 shows the finished UserForm for this example. For best results, start with a new workbook with only one worksheet in it. Then follow these steps:
1. Press Alt+F11 to activate the VBE.
2. In the Project window, select the empty workbook and choose Insert➪UserForm.
An empty UserForm is added to the project.
|
|
|
3. Change the UserForm’s Caption property to Get Name and Sex. If the Properties window isn’t visible, press F4.
to.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture № 13. Accessing Your Macros through the User Interface
In This Chapter
1. Getting a sneak peek into customizing the Ribbon using XML
2. Adding items to a right-click menu
Вefore Office 2007, there was no such thing as a Ribbon. Back then, people used drop-down menus and toolbars. Now, the Ribbon is the user interface for Microsoft Office.You might expect to be able to create custom Ribbon commands using VBA. The bad news is that you can’t use VBA to modify the Ribbon. The good news is that you’re not completely out of luck. This chapter describes some of the ways to work with Excel’s user interface.
Ribbon Customization
This section describes ways to customize the Ribbon. You can modify the Ribbon manually, but you cannot make changes to the Ribbon by using VBA. Sad, but true. For example, if you write an application and you’d like to add a few new buttons to the Ribbon, you need to program those changes outside of Excel, using something called RibbonX.
Customizing the Ribbon manually
It’s very easy to make changes to the Ribbon manually, but you must be using Excel 2010 or Excel 2013. If you use Excel 2007, you should just skip this section because it doesn’t apply to you.
You can customize the Ribbon in these ways:
✓ Tabs
• Add a new custom tab.
• Delete custom tabs.
• Add a new group to tab.
|
|
|
• Change the order of the tabs.
• Change the name of a tab.
• Hide built-in tabs.
✓ Groups
• Add new custom groups.
• Add commands to a custom group.
• Remove commands from custom groups.
• Remove groups from a tab.
• Move a group to a different tab.
• Change the order of the groups within a tab.
• Change the name of a group.
That’s a fairly comprehensive list of customization options, but there are some actions that you cannot do (no matter how hard you try):
✓ You cannot remove built-in tabs — but you can hide them.
✓ You cannot remove commands from built-in groups.
✓ You cannot change the order of commands in a built-in group.
You make manual changes to the Ribbon in the Customize Ribbon panel of the Excel Options dialog box (see Figure 19-1). The quickest way to display this dialog box is to right-click anywhere in the Ribbon and choose Customize The Ribbon.
The process of customizing the Ribbon is very similar to customizing the Quick Access toolbar, which is described later in this chapter. The only difference is that you need to decide where to put the command within the Ribbon. The general procedure is
Дата добавления: 2019-03-09; просмотров: 249; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!