Microsoft KB Archive/116118

= Visual Basic Example of Spinner Use on a Date =

Article ID: 116118

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Excel 5.0a for Macintosh
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 95a
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q116118





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

 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. Use the tools on the Forms toolbar to create one edit box and one spinner on the dialog sheet.  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  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. 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. 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.</li></ol>

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.

<div class="references_section">

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 5.00a 5.00c XL98 XL97 XL7 XL5 XL

Keywords: kbhowto kbprogramming kbdtacode KB116118

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.