XL98: Visual Basic Example for Using SpinButton with a Date

ID: Q189054

The information in this article applies to:

SUMMARY

In Microsoft Excel 98, you can use a spin button on a user form to change values on a worksheet or in a Visual Basic procedure. Microsoft Visual Basic for Applications provides the ability to attach procedures to object events.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

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.

   a. On the Insert menu, click UserForm.

   b. Click the TextBox control from the Toolbox. Insert a text box
      (TextBox1) on the user form (UserForm1).

   c. Click the SpinButton control from the Toolbox. Insert a
      spin button (SpinButton1) on the user form (UserForm1).

   d. 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.

   a. Click the command button. Click the Caption, type
      "Close Now" (without the quotation marks), and press RETURN.

   NOTE: This places the text on the command button.

   b. Click to select the form. In the Properties Window, click Caption,
      type "Change Date" (without the quotation marks), and press RETURN.

5. Type the code for the objects, and follow these steps:

   a. 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

   b. On the Window menu, click "UserForm1 (UserForm)."

   c. On the user form, click the spin button. On the View
      menu, click Code.

   d. Ensure that the Object list (the list in the upper left side of the
      window) shows "SpinButton1" (without the quotation marks). 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

   e. 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.

REFERENCES

For more information about the spin button control, from the Visual Basic Editor, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Excel Help, type the following text

   spinbutton

and then click Show Topics. Select the "SpinButton Control" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: 8.00 XL98 Spin Button

Keywords          : kbprg kbdta xlvbahowto 
Version           : MACINTOSH:98
Platform          : MACINTOSH
Issue type        : kbhowto

Last Reviewed: May 18, 1999