 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
TextBox and SpinButtons
The Control Toolbox within the Visual Basic Editor by default will hold only the most commonly used controls. With just these Controls you will often find that they can be manipulated and programmed to do most if not all of what would be expected of most projects. Take for example the TextBox Control (possibly the most frequently used Control). Just because it is called a TextBox, by no means means that we are limited to only using text in this Control. While the default for this Control is to return text, it can also just as easily return numeric values.
Let's now look at some examples of how we could use a TextBox.
Most often a TextBox Control would be used to collect typed information from a user. In previous lessons we looked at the fact that the user may mistype their information so we need to include as much validation checking as we possibly can. Obviously if the user will be typing their name there is no validation check that we can really do unless of course we have a list of all possible names that would be entered into it in which case we would end up using a ComboBox to store these names. Let's assume for a moment that we are going to use our TextBox in this instance to collect a numeric value from the user. We could simply allow the user to type in a number and/or use the SpinButton Control to increment up to the desired number.
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.
Try this simple example to help better understand the SpinButton Control.
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
Take a moment to look through these properties and you will notice that most of them are common to many other Controls.
Double click the SpinButton Control and within the default Change Event type TextBox1.Value=SpinButton1.Value
Now run your UserForm and user your SpinButton control to increment or decrement a number.
You will notice that by default the lowest value we can reach is 0, with the highest value being 100. If you have not guessed already, this can be easily changed in the Properties window for the SpinButton under the Max and Min Properties. So let's go into this spot now and change this minimum and maximum to any two numbers. While you are there, also notice the Property called SmallChange. This simply tells our SpinButton what value to increment by. The default is 1, but we can change this to any value that fits within the scope of a Long, ie; -2, 147, 483, 648 to 2, 147, 483, 647. But having said this the recommended range of values ( (according to Excel's help) is from -32,767 to 32,767. Let's change the SmallChange Property of the SpinButton to 2. Now run your UserForm again and use the SpinButton to increment or decrement your number. This time you will be incrementing up by a step value of 2 until you reach the value set in your Max Property and down until you reach the Min Property set. This is the use of the SpinButton in possibly its simplest form. But don't confuse this as being the same as the SpinButton being a very simple control and not having much scope of purpose. If you think about it, the whole basis of computers and Excel especially are simply built on numeric values. By this I mean that we could use the SpinButton Control to allow the user to do almost anything and you will be surprised at how many users find it more convenient to increment up/down to a number rather than type it. Not only does this help the User, but it can also aid us greatly in the fact that we can validate the TextBox storing the number to fit between a certain range and pattern without having to put in some lengthy code to validate the number chosen.
To ensure that the user cannot type into the TextBox a specific number, all we need to do is either change the Enabled Property of the TextBox to FALSE and/or the Locked Property. Once we have done this we can assume as safely as possible that the number chosen by the user using the SpinButton will meet any given criteria that we have determined.
Let us now assume we have a scenario where we have a TextBox always defaulting to today's date and we want the user 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. Let us use an example now with our TextBox to see how we can use the SpinButton to do this safely.
Double click the UserForm
Change the default Click Event to the Initialise Event
Within here place TextBox1.Value = Format(Date, "ddd d mmm yyyy")
Above your TextBox add a Label Control and change its Caption Property to read 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 your UserForm
As soon as the UserForm shows our TextBox will display the current date including the name of the current day. Now use your SpinButton control to increment the date by 1 day at a time. You will notice that the date in the TextBox will increment by one day each time. Using this method you can ensure that the User will not type an invalid date that is not recognized by Excel or miscalculate the date from the number of days chosen. In fact, it would be impossible to do so.
Calendar Control
One Control that Excel has which is not shown by default is the Calendar Control. This is a very handy Control to use when dates are required to be collected from the user. Not only does it make our life easier but also the user, as they can visually see a real calendar.
What I would like you to do using all available resources is create a small project that would allow a user to nominate certain dates and have them passed back to specific cells as shown in the attached Workbook. I am purposely NOT telling you how this Control works as it is very important at this level you are able to work out problems and overcome them.
Once you have had a go at this go here for some examples etc of this great Control:
http://www.microsoft.com/exceldev/articles/CalExcel.htm