|
|
SpinButtons
The SpinButton Control simply increments and decrements a number. The default property for a SpinButton is the Value property, while the default event for a SpinButton is the Change event. Some default properties are Min 0, Max 100, and SmallChange (step size) 1. These can be changed to any value that fits within the scope of a Long, ie -2,147,483,648 to +2,147,483,647, though the Excel Helps recommend a range of values is from –32,767 to +32,767.
EXERCISE
· Insert a UserForm .
· On it place a TextBox.
· To the right of the Textbox place a SpinButton.
· Right click on the SpinButton and select Properties to display the Property Window for the SpinButton
· Change Min to -3, Max to 4 and SmallChange to 2.
· Double click the SpinButton Control and within the default Change Event type TextBox1.Value=SpinButton1.Value
· Run UserForm and use SpinButton to increment or decrement the number. Note that when there is not enough left to accommodate the full step size, the number goes to Max or Min
Many users find it more convenient to change a number with a SpinButton rather than type it. It’s easier for the programmer, too, since the number is known to be valid without lengthy validation code.
EXERCISE
Assume a TextBox defaulted to today's date and the user is to be able to increment that date by one day up to any day one year ahead. To allow the user to simply add say 21 days to a date, is fraught with potential disasters. This example shows how the SpinButton can do this safely.
· Double click the UserForm
· Change the default Click Event to the Initialize Event and within here place TextBox1.Value = Format(Date, "ddd d mmm yyyy")
· Above the TextBox add a Label control and change its caption to today's date.
· Now within the SpinButton1_Change Event change our existing code to TextBox1.Value=Format(Date + SpinButton1.Value, "ddd d mmm yyyy") and below this type Label1.Caption=SpinButton1.Value & " day(s) from now"
· Change the Min Property of the SpinButton to 0 and the Max Property of the SpinButton to 365
· Increase the TextBox width so it will display the date in the format chosen
· Run UserForm
The UserForm starts with the TextBox displaying the current date, including the name of the day of the week. Use the SpinButton to increment the date. Note that the date in the TextBox will increment by one day each time, to the maximum of today + 365 days. Using this method ensures that the user will not type an invalid date nor will the program miscalculate the date. In fact, it would be impossible to do so.
TabStrips and MultiPages
Both TabStrips and MultiPages are useful for displaying records from a database where each record has many fields.
TabStrips display a record for each tab. Each tab displays the same fields. The TabStrip is best where some small number of fields is to be displayed, because of the limited number of controls which fit on a UserForm before it looks cluttered. The big advantage of TabStrips is that all the records available can be seen at a single glance. Additional tabs can be created by code, but the maximum is somewhat limited by the number of tabs which can be displayed. Depending on the width of the control and the text length of the tab captions, perhaps 6 to 10 records can be displayed on a single row across the top or bottom, and up to 20 records by turning on the MultiRow property. More can be accommodated by showing the tabs on the left or right.
MultiPages display multiple pages for each record. Each page displays different fields, so it is the control of choice when many fields are to be displayed for each record, or if the database has too many records to display each on its own tab. Another control such as ComboBox or ListBox is then required to select the record to display.
These are the most complex of the standard controls because they require extra steps in initialization and when switching records. Fields for both controls are usually set at design time. Unless the number of records to be displayed is known at design time, then adding more tabs at run time might make TabStrips a little more difficult to use.
TabStrip is a collection of tab objects and MultiPage is a collection of page objects.
TabStrip Control
To designing a TabStrip, two questions must be answered:
1. How many tabs are needed?
2. Will the number of tabs vary from one time to the next?
If the number can vary, then design for the minimum number of tabs, and have the initialize procedure add the rest. Each tab object is populated when its tab is clicked.
Private Sub UserForm_Initialize()
‘ set required tab count
‘ for count to required tab count
‘ add one tab
‘ for 1 to required count
‘ set each tab caption
End Sub
Private Sub TabStrip1_Click()
‘ set fields
End Sub
EXAMPLE
From the Lesson 3 sheet of the demo file, click the “TabStrip and MultiPage Demos” button for an example. It is initialized with the first four records of the Lesson 2 Demo Database. Clicking a tab displays the fields for that record.
MultiPage Control
A common use of MultiPage is to display a record with enough fields that they look cluttered on a UserForm, or with so many fields that they won’t even fit. A problem, of course, is dividing the fields to be displayed into pages. Grouping fields by function, or by topic, or by order to be entered, will help the user to intuitively understand what is being shown. Many fields will group naturally, but there will always be some that are one-of-a-kind. Frequent use of use of labels will help make clear what is being shown on each page.
Using MultiPage to display records of a database requires a method to quickly locate the record to be displayed. For smaller databases, this could be a ComboBox or ListBox. The automatic ScrollBar will allow quick navigation bring the record into view where it can be clicked. For larger databases, the choice depends on whether the main field is sorted or not. If sorted, then use a TextBox to enter and a ListBox to display. Typing characters into the TextBox causes the ListBox to be loaded with all the records where the beginning of the field matches the characters so far. Eventually enough characters will be typed to shorten the list to where the user can locate the record. If the main field is not sorted, then the contents of the TextBox will be used as a search parameter to locate records. The search text can include wild cards such as “*” and “?” and the search method can even be expanded to include values from multiple fields.
Picking the method of selecting a record often draws heavily on the experience of the designer, but must also consider the preferences of the client. Searches are more powerful, but are more difficult to use (and to code!) and not likely to serve well an inexperienced client.
EXAMPLE
From the Lesson 3 sheet of the attached file UserForms Lesson 3 2007.xlsx, click the “TabStrip and MultiPage Demos” button for an example. The ComboBox used for selecting a record is initialized with the first four records of the Lesson 2 Demo Database. Clicking a record displays the fields for that record. Clicking each Page shows the data of that Page, in this amazingly simply example is just one field.
Besides displaying records from a database, MultiPage can be used to behave in the same manner as any one of Excel's standard Wizards. A Wizard by definition is an aid that steps through a particular process. An example of this would be the Pivot Table Wizard or Chart Wizard. A significant exercise implementing a wizard with MultiPage is included in Lesson 4.
Entering or Editing Records with MultiPage
Typically databases have three user functions: add, delete, and display/edit. A useful variation of display/edit is to add a new record based on an old record. To do this after the edit is complete, the user must be offered an add function as well as a save function.
Image
The image control allows display of image files. All the common file types are supported, including JPG, BMP, and GIF. The displayed file can’t be edited, but if the file doesn’t match the size of the image control, it has a variety of properties to control how the file is displayed. These properties are AutoSize, PictureAlignment, and PictureSizeMode.
EXAMPLE
From the Lesson 3 sheet of the attached file UserForms Lesson 3 2007.xlsx, click the “SpinButton and Image Demo” button for examples. A SpinButton cycles through 3 sample files, and ComboBoxes allow effects of the properties to be investigated.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.