Per gaftalik:
I dont know much about VBA but able to paste a code if someone can
explains .
Here's something I use in MS Access. Dunno how
portable to Excel the code is though. In this
case, it's responding to Up/Down arrow keys.
I guess Excel has some sort of spin control,
but the idea would be the same
--------------------------------------------
Public Sub SpinDate(ByRef theTextBox As Control, ByRef theKeyCode As Integer,
ByVal theShiftStatus As Integer, ByVal theNumberOfDays As Long)
debugStackPush mModuleName & ": SpinDate"
On Error GoTo SpinDate_err
' PURPOSE: To increment/decrement date in the specified text box if user has
pressed
' one of the arrow keys AND if the control is enabled
' ACCEPTS: - Pointer to text box in question
' - Code of key pressed by user
' - Shift status of keyboard
' - Number of days to increment/decrement
' SETS: - Specified text box to a formatted date
' - KeyCode to zero if date changed, effectively swallowing the
keystroke
'
' NOTES: 1) Intended to be called from a date field's keyDown event.
' Code in the keyDown should look like this:
' +------------------------------------------------
' |
' | SpinDate Screen.ActiveControl, KeyCode, Shift, 1
' |
' +------------------------------------------------
' 2) We do not want to co-opt Alt/Ctrl key combinations because that
would
' effectively disable things like the user's pressing Alt+S to save
' or Alt+M to commit, hence the altDown/ctrlDown coding.
Dim altDown As Integer
Dim ctrlDown As Integer
altDown = (theShiftStatus And acAltMask)
ctrlDown = (theShiftStatus And acCtrlMask)
If (theTextBox.Locked = False) And (theTextBox.Enabled = True) Then
If (altDown = False) And (ctrlDown = False) Then
If theNumberOfDays = 30 Then 'Special case: taken to be request to move
by a full month
If IsDate(theTextBox.Value) Then
Select Case theKeyCode
Case vbKeyRight ', vbKeyUp ' We use KeyUp/Down for walking
the list
theTextBox.Value = DateAdd("m", 1, theTextBox.Value)
theKeyCode = 0
Case vbKeyLeft
theTextBox.Value = DateAdd("m", -1, theTextBox.Value)
theKeyCode = 0
End Select
Else
Select Case theKeyCode
Case vbKeyRight, vbKeyLeft ', vbKeyDown, vbKeyUp ' We
use KeyUp/Down for walking the list
theTextBox.Value = VBA.Format$(Date, "mm/dd/yyyy")
theKeyCode = 0
End Select
End If
Else
If IsDate(theTextBox.Value) Then
Select Case theKeyCode
Case vbKeyRight ', vbKeyUp ' We use KeyUp/Down for walking
the list
theTextBox.Value = DateAdd("d", theNumberOfDays,
theTextBox.Value)
theKeyCode = 0
Case vbKeyLeft
theTextBox.Value = DateAdd("d", -1 * theNumberOfDays,
theTextBox.Value)
theKeyCode = 0
End Select
Else
Select Case theKeyCode
Case vbKeyRight, vbKeyLeft ', vbKeyDown, vbKeyUp ' We
use KeyUp/Down for walking the list
theTextBox.Value = VBA.Format$(Date, "mm/dd/yyyy")
theKeyCode = 0
End Select
End If
End If
End If
End If
SpinDate_xit:
DebugStackPop
On Error Resume Next
Exit Sub
SpinDate_err:
BugAlert True, ""
Resume SpinDate_xit
End Sub
