Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Change Macro | Excel Worksheet Functions | |||
Date change button/macro | Excel Programming | |||
Run a macro with date/cell change | Excel Programming | |||
macro to change date ranges | Excel Programming | |||
Date change macro between worksheets | Excel Programming |