XL98: Visual Basic Example for Using SpinButton with a Date (189054)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following Visual Basic procedure shows you how to use a spin button control to increment and decrement a date value. To create and run this procedure, follow these steps:

1. Open or create a new workbook in Microsoft Excel 98, and start the Visual Basic Editor by pressing OPTION+F11.
2. Create the user form.
   
   
   1. On the Insert menu, click UserForm.
   2. Click the TextBox control from the Toolbox. Insert a text box (TextBox1) on the user form (UserForm1).
   3. Click the SpinButton control from the Toolbox. Insert a spin button (SpinButton1) on the user form (UserForm1).
   4. Click the CommandButton control from the Toolbox. Insert a command button (CommandButton1) on the user form (UserForm1).
3. On the View menu, click Properties Window.
4. Type new captions for the form objects.
   
   
   1. Click the command button. Click the Caption, type Close Now, and press RETURN.
      
      NOTE: This places the text on the command button.
   2. Click to select the form. In the Properties Window, click Caption, type Change Date, and press RETURN.
5. Type the code for the objects, and follow these steps:
   
   
   1. Click to select the command button. On the View menu, click Code. Two lines of code are automatically entered for you. Between those two lines, type the following:

            UserForm1.Hide
      								

   2. On the Window menu, click "UserForm1 (UserForm)."
   3. On the user form, click the spin button. On the View menu, click Code.
   4. Ensure that the Object list (the list in the upper left side of the window) shows SpinButton1. Click the Procedures list (the list in the upper right of the window) and select SpinDown. Type the following code between the two lines placed automatically on the sheet:

               UserForm1.TextBox1.Text = DateValue(UserForm1.TextBox1.Text) - 1
      								

   5. Click the Procedures list and select SpinUp. Type the following code between the two lines of code placed automatically on the sheet:

               UserForm1.TextBox1.Text = DateValue(UserForm1.TextBox1.Text) + 1

6. On the Insert menu, click Module. On the module sheet, type the following code:

         Sub ShowForm()
             UserForm1.TextBox1.Text = Date
             UserForm1.Show
         End Sub
   						

7. On the Tools menu, click Macros. In the Macros dialog box, click ShowForm and then click Run.

The user form is displayed. Click the up arrow to increment the date,and click the down arrow to decrement the date. To close the user form and return to the Microsoft Visual Basic Editor, click Close Now.