As you know, I love keyboard shortcuts. But I hate entering dates. So I created this little helper. In my new accounting system, they also have shortcut keys for incrementing weeks, months, and years. I don’t have much use for incrementing weeks, but I could kick myself for not thinking of the others. Plus Alt+semi-colon is just sitting there doing nothing.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
Public Sub IncrementMonth() On Error Resume Next gclsAppEvents.AddLog "%;", "IncrementMonth" If IsDate(ActiveCell.Value) Then ActiveCell.Value = DateSerial(Year(ActiveCell.Value), Month(ActiveCell.Value) + 1, Day(ActiveCell.Value)) ElseIf IsTextTime(ActiveCell.Text) Then ActiveCell.Value = ActiveCell.Value + 1 / 24 End If End Sub Public Sub DecrementMonth() On Error Resume Next gclsAppEvents.AddLog "+%;", "DecrementMonth" If IsDate(ActiveCell.Value) Then ActiveCell.Value = DateSerial(Year(ActiveCell.Value), Month(ActiveCell.Value) - 1, Day(ActiveCell.Value)) ElseIf IsTextTime(ActiveCell.Text) Then ActiveCell.Value = ActiveCell.Value - 1 / 24 End If End Sub Public Sub IncrementYear() On Error Resume Next gclsAppEvents.AddLog "^%;", "IncrementYear" If IsDate(ActiveCell.Value) Then ActiveCell.Value = DateSerial(Year(ActiveCell.Value) + 1, Month(ActiveCell.Value), Day(ActiveCell.Value)) ElseIf IsTextTime(ActiveCell.Text) Then ActiveCell.Value = ActiveCell.Value + 1 End If End Sub Public Sub DecrementYear() On Error Resume Next gclsAppEvents.AddLog "^+%;", "DecrementYear" If IsDate(ActiveCell.Value) Then ActiveCell.Value = DateSerial(Year(ActiveCell.Value) - 1, Month(ActiveCell.Value), Day(ActiveCell.Value)) ElseIf IsTextTime(ActiveCell.Text) Then ActiveCell.Value = ActiveCell.Value - 1 End If End Sub |
Alt+semi-colon is not really sitting there doing nothing. It is the default shortcut to select visible cells only, which can be a real life-saver in those cases where Excel is inconsistent about whether or not it will copy or otherwise interact with the cells that are currently hidden by a filter or other operation.
Dang. Nice catch David. I really should have searched for that instead of just trying it out on some Excel sheets (that obviously didn’t have hidden data). It still may be worth it to me but I really don’t like replacing built-in shortcuts except when it replaces the built-in function. Maybe I’ll get rid of the IncrementYear procedure and use that for months. Thanks for catching that.
there’s no need to check for text, because isdate already checks that (whether a string can be interpreted as date).
I’d prefer to use the buitlin VBA functions:
Probably more efficient and correct: (in the previous code I used “y”, but I should have used “yyyy”)
The combination of year & hour
And the conbination of month & day
Very nice.