[ Pobierz całość w formacie PDF ]
.Add a CommandButtonobject to the UserForm and then change the followingproperties for the CommandButton:Property Change ToName OKButtonCaption OKDefault True5.Add another CommandButtonobject and then change the following properties:Property Change ToName CancelButtonCaption CancelCancel True6.Add an OptionButton control and then change the following properties.(Thisoption is the default, so its Valueproperty should be set to True.)Property Change ToName OptionUpperCaption Upper CaseValue True7.Add a second OptionButton control and then change the following properties:Property Change ToName OptionLowerCaption Lower Case 43 539671 ch36.qxd 8/28/03 10:06 AM Page 728Part VI &' Programming Excel with VBA7288.Add a third OptionButton control and then change the following properties:Property Change ToName OptionProperCaption Proper Case9.Adjust the size and position of the controls and the form until your UserFormresembles the UserForm shown in Figure 36-10.Make sure that the controlsdo not overlap.Figure 36-10: The UserForm after addingcontrols and adjusting some properties.TipThe Visual Basic Editor provides several useful commands to help you size andalign the controls.Select the controls that you want to work with, and then choosea command from the Format menu.These commands are fairly self-explanatory,and the online Help has complete details.Testing the UserFormAt this point, the UserForm has all the necessary controls.What s missing is a wayto display the form.This section explains how to write a VBA procedure to displaythe UserForm.1.Make sure that the VBE window is activated.2.Insert a module by selecting Insert ª' Module.3.In the empty module, enter the following code:Sub ChangeCase()CaseChangerDialog.ShowEnd Sub4.Select Run ª' Sub/UserForm (or press F5). 43 539671 ch36.qxd 8/28/03 10:06 AM Page 729Chapter 36 &' Creating UserForms729The Excel window is then activated, and the new UserForm is displayed, as shownin Figure 36-11.The OptionButtons work, but clicking the OK and Cancel buttonshas no effect.These two buttons need to have event handler procedures.Click theClose button in the title bar to dismiss the UserForm.Figure 36-11: Displaying the UserForm.Creating event handler proceduresThis section explains how to create two event handler procedures: one to handle theClickevent for the CancelButton CommandButton and the other to handle the Clickevent for the OKButton CommandButton.Event handlers for the OptionButtons arenot necessary.The VBA code can determine which of the three OptionButtons isselected.Event handler procedures are stored in the UserForm code module.To create theprocedure to handle the Clickevent for the CancelButton, follow these steps:1.Activate the CaseChangerDialog form by double-clicking its name in theProject window.2.Double-click the CancelButton control.3.VBE activates the module for the form and inserts an empty procedure.4.Insert the following statement before the End Substatement:Unload CaseChangerDialogThat s all there is to it.The following is a listing of the entire procedure:Private Sub CancelButton_Click()Unload CaseChangerDialogEnd SubThis procedure is executed when the CancelButton is clicked.It consists of a singlestatement that unloads the CaseChangerDialog form. 43 539671 ch36.qxd 8/28/03 10:06 AM Page 730Part VI &' Programming Excel with VBA730The next step is to add the code to handle the Clickevent for the OKButtoncontrol.Follow these steps:1.Select OKButton from the drop-down list at the top of the module.VBE begins a new procedure called OKButton_Click.2.Enter the following code.The first and last statements have already been entered for you by VBE.Private Sub OKButton_Click()Application.ScreenUpdating = False Exit if a range is not selectedIf TypeName(Selection) Range Then Exit Sub Upper caseIf OptionUpper ThenFor Each cell In SelectionIf Not cell.HasFormula Thencell.Value = StrConv(cell.Value, vbUpperCase)End IfNext cellEnd If Lower caseIf OptionLower ThenFor Each cell In SelectionIf Not cell.HasFormula Thencell.Value = StrConv(cell.Value, vbLowerCase)End IfNext cellEnd If Proper caseIf OptionProper ThenFor Each cell In SelectionIf Not cell.HasFormula Thencell.Value = StrConv(cell.Value, bProperCase)End IfNext cellEnd IfUnload CaseChangerDialogEnd SubThe macro starts by turning off screen updating.(This makes the macro run faster.)Next, the code checks the type of the selection.If a range is not selected, the proce-dure ends.The remainder of the procedure consists of three separate blocks.Onlyone block is executed, determined by which OptionButton is selected.The selectedOptionButton has a value of True.Finally, the UserForm is unloaded (dismissed).Testing the UserFormTo try out the UserForm, follow these steps:1.Activate Excel. 43 539671 ch36.qxd 8/28/03 10:06 AM Page 731Chapter 36 &' Creating UserForms7312.Enter some text into some cells.3.Select the range with the text.4.Choose Tools ª' Macro ª' Macros (or press Alt+F8).5.In the Macros dialog box, select ChangeCase from the list of macros and thenclick OK.The UserForm appears.6.Make your choice and click OK.Try it with a few more selections.Notice that if you click Cancel, the UserForm isdismissed, and no changes are made
[ Pobierz całość w formacie PDF ]