XL: Visual Basic Example of Spinner Use on a Date

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

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 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 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/default.asp

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.

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

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

Microsoft Excel 5.0 for Windows and Macintosh

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

  spinners, creating