Microsoft Office Excel 2003 Bible

[ Pobierz całość w formacie PDF ]
.A variable (Msg) and the concatenation operator (&) are used to build themessage in a series of statements.In the second statement, vbNewLineis a con-stant that represents a line feed character.(Using two line feeds inserts a blankline.) The title argument is also used to display a different title in the message box.Figure 36-5 shows how this message box appears when the procedure is executed.43 539671 ch36.qxd 8/28/03 10:06 AM Page 720Part VI &' Programming Excel with VBA720Figure 36-5: A message box with a longermessage and a title.Creating UserForms: An OverviewThe InputBoxand MsgBoxfunctions do just fine for many cases, but if you need toobtain more information, you need to create a UserForm.Following is a list of the general steps that you typically take to create a UserForm:1.Determine exactly how the dialog box is going to be used and where it is to fitinto your VBA macro.2.Activate the Visual Basic Editor and insert a new UserForm.3.Add the appropriate controls to the UserForm.4.Create a VBA macro to display the UserForm.5.Create event handler VBA procedures that are executed when the user manip-ulates the controls (for example, clicks the OK button).The following sections provide more details on creating a UserForm.Working with UserFormsTo create a UserForm, you must first insert a new UserForm in the Visual BasicEditor window.To activate the Visual Basic Editor, select Tools �' Macro �' VisualBasic Editor (or press Alt+F11).Make sure that the correct workbook is selected inthe Project window and then select Insert �' UserForm.The Visual Basic Editor dis-plays an empty UserForm, as shown in Figure 36-6.When you activate a UserForm,the Visual Basic editor displays the Toolbox, which is used to add controls to theUserForm.Adding controlsThe Toolbox, also shown in Figure 36-6, contains various ActiveX controls that youcan add to your UserForm.When you move the mouse pointer over a control in the Toolbox, the control sname is displayed.To add a control, click and drag it in the form.After adding acontrol, you can move it or change its size.43 539671 ch36.qxd 8/28/03 10:06 AM Page 721Chapter 36 &' Creating UserForms721Figure 36-6: An empty UserForm.Table 36-2 lists the Toolbox controls.Table 36-2Toolbox ControlsControl DescriptionSelect Objects Lets you select other controls by draggingLabel Adds a label (a container for text)TextBox Adds a text box (allows the user to type text)ComboBox Adds a combo box (a drop-down list)ListBox Adds a list box (to allow the user to select an item from a list)CheckBox Adds a check box (to control Boolean options)OptionButton Adds an option button (to allow a user to select from multipleoptions)ToggleButton Adds a toggle button (to control Boolean options)Frame Adds a frame (a container for other objects)CommandButton Adds a command button (a clickable button)TabStrip Adds a tab strip (a container for other objects)MultiPage Adds a multipage control (a container for other objects)ScrollBar Adds a scroll bar (to specify a value by dragging a bar)SpinButton Adds a spin button (to specify a value by clicking up or down)Image Adds a control that can contain an imageRefEdit Adds a reference edit control (lets the user select a range)43 539671 ch36.qxd 8/28/03 10:06 AM Page 722Part VI &' Programming Excel with VBA722Cross- You can also place some of these controls directly on your worksheet.Refer toReferenceChapter 37 for details.Changing the properties of a controlEvery control that you add to a UserForm has several properties that determinehow the control looks and behaves.You can change some of these properties (suchas Heightand Width) by clicking and dragging the control s border.To changeother properties, use the Properties window.To display the Properties window, select View �' Properties Window (or press F4).The Properties window displays a list of properties for the selected control.(Eachcontrol has a different set of properties.) If you click the form itself, the Propertieswindow displays properties for the form.Figure 36-7 shows the Properties windowfor a CommandButton control.Figure 36-7: The Properties window for aCommandButton control.To change a property, select the property in the Property window and then enter anew value.Some properties (such as BackColor) enable you to select a propertyfrom a list.The top of the Properties window contains a drop-down list that enablesyou to select a control to work with.You can also click a control to select it and dis-play its properties.When you set properties by using the Property window, you re setting properties atdesign time.You can also use VBA to change the properties of controls while theUserForm is displayed (that is, at run time).43 539671 ch36.qxd 8/28/03 10:06 AM Page 723Chapter 36 &' Creating UserForms723A complete discussion of all the properties is well beyond the scope of this book.To find out about a particular property, select it in the Property window and pressF1.The online Help for UserForm controls is extremely thorough.Handling eventsWhen you insert a UserForm, that form can also hold VBA Sub procedures to han-dle the events that are generated by the UserForm.An event is something thatoccurs when the user manipulates a control.For example, clicking a button causesan event.Selecting an item in a list box control also triggers an event [ Pobierz całość w formacie PDF ]

download - pobieranie - ebook - do ÂściÂągnięcia - pdf

Menu