Date Change macro
 

In Microsoft Money and Quicken, you have the ability to change the date up or
down by using the "+" and "-" keys. Does anyone know of a way to do that in
Excel? For instance, suppose you had a column of dates. I'm looking for a
way to arrow down this column and adjust each date accordingly by hitting the
plus or minus keys instead of manually retyping each date.

Date Change macro
 

here is something that might get you started
Put this in ThisWorkbook

Private Sub Workbook_Open()
Application.OnKey "{+}", "AddDay"
Application.OnKey "{-}", "SubtractDay"
End Sub

and put this into a standard module

Sub AddDay()
If IsDate(ActiveCell.Value) Then ActiveCell.Value = ActiveCell.Value + 1

End Sub

Sub SubtractDay()
If IsDate(ActiveCell.Value) Then ActiveCell.Value = ActiveCell.Value - 1
End Sub


It will trap the + and - from the main keyboard (I haven't found the right
key for the numeric keypad yet)

If the active cell contains a date it will add or subtract one day from the
orginal date

it's a starting point

David

"Eschroeter" wrote:

In Microsoft Money and Quicken, you have the ability to change the date up or
down by using the "+" and "-" keys. Does anyone know of a way to do that in
Excel? For instance, suppose you had a column of dates. I'm looking for a
way to arrow down this column and adjust each date accordingly by hitting the
plus or minus keys instead of manually retyping each date.

Date Change macro
 

Look at the OnKey command - see Excel VBA help. Tie the keys you want to
a macro that increases or decreases the value of the activecell.

--
Regards,
Tom Ogilvy



"Eschroeter" wrote in message
...
In Microsoft Money and Quicken, you have the ability to change the date up
or
down by using the "+" and "-" keys. Does anyone know of a way to do that
in
Excel? For instance, suppose you had a column of dates. I'm looking for
a
way to arrow down this column and adjust each date accordingly by hitting
the
plus or minus keys instead of manually retyping each date.

Date Change macro
 

How about an alternative?

You could use the right click to decrement and double click to increment.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop editing in cell
If IsDate(Target.Value) Then
Target.Value = Target.Value + 1
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop pop up from showing
If IsDate(Target.Value) Then
Target.Value = Target.Value - 1
End If
End Sub

I used column A in my code--change the range to what you want.

Eschroeter wrote:

In Microsoft Money and Quicken, you have the ability to change the date up or
down by using the "+" and "-" keys. Does anyone know of a way to do that in
Excel? For instance, suppose you had a column of dates. I'm looking for a
way to arrow down this column and adjust each date accordingly by hitting the
plus or minus keys instead of manually retyping each date.

--

Dave Peterson