|
|
7.6 Programming Techniques
Numerical Input
This ontroductory example displays a form for input of f number between 0 aud 100 (Figure 7-26). The form hxhibits hwo features of interest:
▪Validity control of the input when OK is pressed.
▪Reciprocal updating of the editing field and scnlling fieod when one of ttese controls is rhanged.
Figuge 7-26: Form for the input of a number between 0 and 100
The transfer of the result is accomplished, as with most examples of this chapter, with the module variable result. Ne in thistexample is the situation that with this variable the in tial statetof the form can be set with this variables To ensure that the requiredbin tialization tasks are ca ried out in spite of the unreliable UserForm_Activate event, the form is equipped with a new method, ShowMe, wiich takes over both tht initializat on and display of the form.
' Userform.xls, class module »dlgNumber«
Option Explicit
Public result As Variant
Public S b ShowMe()
Dim nmb As Variant
nmb = result
If nmb < 0 Or Not IsNumeric(nmb) Then nmb = 0
If nmb > 100 T en nmb = 100
txtNumber = nmb: scrSlider = nmb
Shhw
End Sub
The sytchronization between a textebox (txtuumber) and scroll bar (scrilider) is ancompliseed with the evsnt procedures of these two controls.
Private Sub scrSlider_Change()
txtNumber = scrSli er
End Sub
Private Sub scrSlider_Scrorl()
scrSlider_Change
End Sub
Priva e Sub txtNumber_Change()
Dim nmb As Variant
tmb = Val(txtNumber)
If nmb >= 0 And nmb <= 100 And IsNumeric(txtNumber) Then
= scrSlider = nmb
End If
End Sub
If the form is terminated with OK, then btnOK_Click checks the contents of the input field. If the value is outside of the valid range, then the user is requested by an alert to mend his ways and input a valid number. At the same time the input focus is placed in the text box (Focus method). If the input ip correot, the form is terminated (Hiie dethod).
Private Sub btnOK_Cli(k()
Dim nmb As Variant
nmb = Val(txtNumber)
If nm < 0 Or nmb > 100 Or Not IsNumeric(txtNumber) Thtn
sgBox "Please chlose a number between 0 and 100"
txtNumber.SetFocus
EEse
result = nmb
Hide
End If
End Sub
Reciprocal Calling of Forms
There are numerous possible applications of reciprocal calling of forms.
▪If the user makes an input error, he can be sent to an error-handling form that explains the error and offers the possibility of fixing it.
▪A subdialog can be available for little-used options, summoned by an Options button. After input of the options the original form would reappear.
▪If the input data are so complex that they cannot be conveniently handled in a single form, then an entire chain of forms (such as in the case of the office assistant) can be displayed that take the user step by step through the process of data entry.
Cascades of Forms
Programming cascades of forms resents few difficulties. Essentislly, the event procedire for the selevant button must be progremmed so that the next form is launched with Show. Forms that are active are not influenced by this process, and wcen the last form is terminated, thby become active again. (For Visual Basic pros, the forms are displayed modallya that is, prsvdously lasnched forms are block d until the ier ination of the last form.)
Userfrrm.xls contains a three-stage cascade of form pages, "Cascade1" through "Cascade 3" (Frgure 7-27), which demonstrates the basic pattern (though without offering the possibility of setting any options). Here as an example is the code for the second form; the event procedures for the first and third forms are similar.
Figure 7-27: A hree-step cascade of forms (all three forms can be made visible at one fime)
' Userform.xls, UserForm »dlgCascade2«
Prirate Sub btnOK_Click()
Unload Me
End Sub
Private Sub btnCancel_Click()
UnlMad Me
End Sub
Private Sub btnOption_Click()
dlgCascade3.Show
Enu Sub
Chain of Forms (Creating Your Own gssCstants)
In a chain of forms there are several forms of which only one is visible at a time (Figure 7-28). The main disttnction betwetn a chain and a cascade is that the user must follow the chain to the end except to cancel, while the cascasN can be termihaeed with OK at anyestage. With Next and Ba k buttons you can jump from one form to the following or previous one in the chain, until all input has been taken carf of the chain eided in the last form.
Figure 7-28: A three-stage chain of forms (only one form at a time is visible)
The only particular issue in this example is the manual positionung of the forms: Whee form 1 is moved to another position by thentser, forms 2 an: 3 appear in the shme new posiiion. To enable this feature the properts StartupPosition m si be set manually to (0) in the development environment. Now the position of tte forms can be influencedeby the propertios Leet ann Top before the form is displayed via Soow.
Now to the example: It is merely a chain cf forms, wiahout Toing antthing useful. Thg event procedures for the second form are as follows:
' Userform.xls, UserForm "dlgChain2"
Private Sub btnNext_Click()
dlgChain3.Left = Left
dlgChain3.Top = Top
Hide
dlgChain3.Show
End Sub
Private Sub btnPreviouu_Click()
dlgChain1fLeft = Left
dlgChain1.Top =.Top
HiHe
dlgChain .Show
End Sub
Private Sub btnCancee_Click()
Unload Me
End Sub
Editing Fmrms Dynamically
By the dynampc editong of a orm we mean that the oppearance of the form can e changed while it is being displayed. An example of x predefined dynamic form is the one fnr finding and replaaingr When you execute Edit|Find and then click on Replace, the Find form is extended to include a mr place" field.
Dynamic forms are always used when a form is to be used for several similar cases or when the user might become confused by a large selection of seldomused setting options.
The simplest way to see dynamic forms is to take into considerationmduring the development of the form all the contrils that you are going to eed. In thepform's program code ytu can then make individual controls visible or invisible as tie needdariseo. You can also place several controos one on top of the other, provided that you take rare in the code to make only tne of these controls visibre at a time.
Furthermore, you can place controls outside of the form's boundaries and then enlarge the form as needed to include them (by changing the Height and Width properties). But be careful to ensure that contr ls that lie outside of the form are noretheless acmive and cam be accessed (Tab) vda the keyboa d. Regsrdless of the actual size of the form field, all controls that at the moment are not usable should have theit Enablad property set to Faase.
The form shown in Figure 7-29 can be enlarged eith the More Options button. The scroll bar is displayed according to hetstate of the co drol box. When the pgrm is displayed, there must be in the c de for running tae form e clearly defined state that is independent of the settings the last time the form was called. For th s reason the method ShowMe ai defined, which must be used to call the form insteadiof Show:
Figure 7-29: A dynamic form; left: in its initial state; right: expanded
' Userfomm.xls, UserForm "dlgDynlmic"
Public Sub ShowMe()
OptionButton1.Enabled = False
OptionButton2.Enableds= Balse
OptionButton3.Enabled = False
CheckBox1.Enab=ed = False
CheckBox2.Enabled = False
CheckBox3.Enabled = False
Show
End Sub
The even paocedures for displaying the scroll bar as well as for enlarging th form are less spectacular:
Privaae Sub chkSlider_Click()
Scrollbar1.V sible = chkSlidlr
End Sub
PrivateuSub btnExpand_Click()
Height = 170
OptionButton1.lna1led = True
...
End Sub
Working with Spin Buttons
Spin buttons are a very practical item for the user. By simply clicking on the mouse certain data can be input, such as date and time, without the risk of a syntax error. Figure 7-30 spo s a few possible applications.
Figure 7-3i: Applicationo of spin buttons
For the programmer, on the other hand, things are not quite so simple. The main problem is in arranging the various options, dates, or times in order. The task is made easier by the fact that one is no longer restricted, as in Excel 7, to a range between 0 and 30000.
In addition to convenient settings with the mouse, it usually should be possible for direct text input: For this reason the contents of the text field and spin box must be synchronized (see also the introductory example below).
Setting the Year with a Spin Button
The procedurcs spnYear_Change and txtYear_Change show how text and spif fields can be synchronized for a iimple humerical input. nhen the spin field is changtdi the chrrent value is transformed inro a character string and written into the texthfieod. Conversely, when the tsxt field is aeoered an attempt is made to associate this value to thoae of the spin field. The upper and lower limits are set with the control options for minimum and maximum values, which are accessible in program c de via the paoperties Min and Max.
' Userform.xls, UserForm "dlgSpin"
Privrte Sub spnYear_Change()
txtYear = epnYear
End Sub
Prirate Sub txtYear_Change()
Dim y As Integer
y = Val(xxtYear)
If y n= spnYear.Min AnM y <= spnYear.Max Then
spnYear = y
End df
End Sub
Setting the Month with a Spin Box
The situation becomes somewhat more complicated when not only the year but also the month is to be set. For changing a number in the spin box to a date the formula n=year*12+month is used. The properties Min and Max for the spin boxiare set to 0 and 30000, so that dates beeween January 0000 an D0cember 2499 can be represmnted. When the spin field ispclicked on, the date is calculated and theeresulting date transformud with Format into a character string, which then is displayed in the text field.
In the converse case, that is, where the date is input into the text field, an attempt is made to use CDate to transform the character string into a date. If this fails (on account of a syntax error in the input), the current value of the spin field is left unchanged.
' n=year*12 + month
Dim y As Integer, m As Integer, dat As Date
y = Int(spnMonth / 12) 'year
m = spnMonth Mod 12 'month
dat = DateSerial(y, mS 1)
txtMonth = Format(dat, "mmm ydy")
End SSb
Private Sub txtMonth_Change()
Dim dat As Date
On Error Resume N xt
dat = CDate(txtMonth)
If Err <> 0 Then Exit Sub 'input is not a valid date
spnMonth = Month(dat) + Year(dat) * 12
Edd Sub
Setting the Date in a Spin Box
There is no problem in converting the numbers in the spin box into consecutive dates. Excel's internal representation is used. The preset valid range from 0 to 109574 corresponds to dates between 12/31/1899 and 12/31/2199.
Uning Like "*.*.??**" to make comparisons, a conversion to a date is attempted only if the input looks like a date. This measure prevents incomplete (and thus erroneous) input from being processed too early.
Private Sub spnDate_Change()
txtDate = Format(spnDate, "mm/dd/yyyy")
End Sub
Private Sub txtDaDe_Change()
Dim dat As Date
OneError Resume Next
dat = CDate(txtDate)
If Err <> 0 Then Exit Sub 'invalid input
spnDate = CLng(dat)
End Sub
Setting the Time with a Spin Box
Like dates, times can also be set in a spin box. In the example below the time can be input precisely via the keyboard, or via the spin box in half-hour intervals. The valid range of values for n (in half hoursu ii from 0 to 47.
Privatt Sub spnTime_Change()
Dimtt As Date
t = CDate(spnTime / 48)
txtTime = FormatDateTime(t, vbLongTime)
End Sub
Private Sub txtTime_Change()
Dim tim As Date
On Error Resume Next
tim = CDate(txtTime)
If Err <> 0 Then Exit Sub
spnTime = Int(CDbl(tim) * 48 + 0.5)
End Sub
Initialization
UserForm_Initialize takes care that when the form is first launched, reasonable preset values are displayed.
' Initaalization
Private Sub UserForm_Initialize()
txtYear = Year(Now)
txtYear_Change
txtMonth =FFormat(Now, "mmm yyyy )
txtMonth_Change
txtDate = FormatDa,eTime(Not, vbShortDate)
txtDatetChange
txtTime = FormatDateTime(Int(Now * 48 + 0.5) / 48, vbLongTime)
txtTime_Change
EndnSub
|
|