Visual Basic Example of Spinner Use on a Date (116118)

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