XL: Visual Basic Example of Spinner Use on a Date

ID: Q116118

The information in this article applies to:

SUMMARY

In the versions of Microsoft Excel listed at the beginning of this article, you can use a spinner control to change values on a worksheet or in a Microsoft Visual Basic for Applications procedure. In order to use the spinner to increment and decrement a value over a wide range, you must set and use the spinner in conjunction with another value or cell to calculate both positive and negative values.

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 macro (Sub procedure) example uses a spinner control to increment and decrement a date value in an edit box.

Sample Visual Basic Macro Procedure

To create the sample dialog box and macro code, follow these steps:

1. In Microsoft Excel 5.0 and 7.0, create a new workbook, click Macro on 

   the Insert menu, and then click Dialog.

   In Microsoft Excel 97 or Microsoft Excel 98, create a new workbook,
   right-click any sheet tab, and then click Insert. In the Insert dialog,
   click MS Excel 5.0 Dialog, and click OK.
2. Use the tools on the Forms toolbar to create one edit box and one 
   spinner on the dialog sheet.
3. In Microsoft Excel 5.0 and 7.0, click Macro on the Insert menu, and 
   then click Module. In the new module sheet, type the Visual Basic macro
   code in this step.

   In Microsoft Excel 97 or Microsoft Excel 98, press ALT+F11 to activate
   the Visual Basic Editor. (Or, click the Tools menu, point to Macro,
   and then click Visual Basic Editor.) On the Insert menu, click Module.
   In the new module, type the Visual Basic macro code in this step.

      ' Defines variable called "OldSpin" as an integer, and makes it
      ' available to all subroutines.
      Public OldSpin As Integer

      ' This routine is assigned to the spinner to run when you choose the
      ' spinner. It will determine the difference between the current
      ' spinner value and OldSpin, and use the difference to increment or
      ' decrement the date in the edit box.
      Sub SpinDate()

          ' Sets the edit box value based on the difference
          ' between OldSpin and the spinner value.
          DialogSheets(1).EditBoxes(1).Text = _
              DateValue(DialogSheets(1).EditBoxes(1).Text) - OldSpin + _
              DialogSheets(1).Spinners(1).Value

          ' Resets OldSpin to the spinner value.
          OldSpin = DialogSheets(1).Spinners(1).Value

      End Sub

      ' This routine initializes the spinner and OldSpin to a value of
      ' 15000, populates the edit box with today's date, and displays the
      ' dialog box.
      Sub ShowDialog()

          ' Sets OldSpin to 15000.
          OldSpin = 15000

          ' Sets the current spinner value to 15000.
          DialogSheets(1).Spinners(1).Value = OldSpin

          ' Sets the edit box to today's date.
          DialogSheets(1).EditBoxes(1).Text = Date

          ' Displays the dialog box.
          Dialogsheets(1).Show

    End Sub
4. In Microsoft Excel 5.0 and 7.0, click the dialog sheet. 

   In Microsoft Excel 97 or Microsoft Excel 98, press ALT+Q to return to
   Microsoft Excel (or click Close And Return To Microsoft Excel on the
   File menu). Activate the dialog sheet if it is not the active sheet.
5. Right-click (or CONTROL-click) the spinner control on the dialog sheet 
   and click Assign Macro on the shortcut menu. Click the SpinDate macro
   and click OK.
6. In Microsoft Excel 5.0 and 7.0, click Macro on the Tools menu. Click the 
   macro "ShowDialog" and click Run to run the macro.

   In Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools
   menu, and then click Macros. Click the macro "ShowDialog" and click Run
   to run the macro.
The spinner increments or decrements the date in the edit box. (By default, this date is the current date.) You can type a different date manually.

REFERENCES

Microsoft Excel 97

For more information about using spinners, click the Index tab in Microsoft Excel 97 Help, type the following text 

  spinner controls
and then double-click the selected text to go to the "About adding buttons, check boxes, and other controls" topic.

Microsoft Excel 7.0

For more information about using spinners, click the Index tab in Microsoft Excel 7.0 Help, type the following text 

  spinners, creating
and then double-click the selected text to go to the "Summary of controls" topic.

Microsoft Excel 5.0 for Windows and Macintosh

For more information about using spinners, choose the Search button in Help, and type: 

  spinners, creating
Choose the Show Topics button, select the topic "Spinner Button", and choose Go To.

Additional query words: 7.00a 7.00 5.00 5.00a 5.00c XL98 XL97 XL7 XL5 

Keywords          : kbprg kbdta kbdtacode PgmHowto PgmCtrlsStd KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999