PDA

View Full Version : [SOLVED] Can I increase date/time with +/- key



Blinkin
03-01-2005, 01:02 PM
I have a cell with a date value of "4:00:00 PM" and I'd like to be able to hit the + key and increment it by 1 hour (so it would become "5:00:00 PM", then "6:00:00 PM", etc.). Same with - key. I'd like it to subtract an hour. Is that possible? I'm using Excel 2003.

mdmackillop
03-01-2005, 01:27 PM
Give your cell the RangeName "TimeCell" and try the following worksheet sub.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewVal
If Target.Address <> Range("TimeCell").Address Then Exit Sub
Application.EnableEvents = False
NewVal = Target
Application.Undo
Select Case NewVal
Case Is = "+"
Target.Formula = Target + (1 / 24)
Case Is = "-"
Target.Formula = Target - (1 / 24)
Case Else
Target.Formula = Target
End Select
Application.EnableEvents = True
SendKeys "(~)"
End Sub

Blinkin
03-01-2005, 03:05 PM
Thanks. I'm trying this but not sure I'm doing the right thing (noob here).

I think I have the cells named properly (when I select all the ones I'm interested in, it shows TimeCell in the box to the left of the "fx" field).

To add a "worksheet sub", (not sure what I'm doing here, but) I went to Tools->Macro->Visual Basic Editor, clicked on View->Code and pasted what you posted. Now, when I go back to a cell and press the "-" or "+" key, it still puts the string "-" or "+" in the value. What else do I need to make it work?

I appreciate your help with this.

mdmackillop
03-01-2005, 03:17 PM
See the attached sample. Any further questions, let me know.

Blinkin
03-01-2005, 04:31 PM
Ok, it did work before, but the problem was that I defined "TimeCell" to be a list of cells (like B3 - E4). It doesn't seem to work when I define "TimeCell" to be a bunch of cells. Works fine if, like in your example, I define "TimeCell" to be one cell. Any idea how to make it work with a range of cells? I realize that that wasn't in my original problem statement.

mdmackillop
03-01-2005, 04:45 PM
A slight change to check all cells in the range


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewVal
If Intersect(Target, Range("TimeCell")) Is Nothing Then Exit Sub
Application.EnableEvents = False
NewVal = Target
Application.Undo
Select Case NewVal
Case Is = "+"
Target.Formula = Target + (1 / 24)
Case Is = "-"
Target.Formula = Target - (1 / 24)
Case Else
Target.Formula = Target
End Select
Application.EnableEvents = True
SendKeys "(~)"
End Sub

Zack Barresse
03-01-2005, 05:03 PM
If you want to change only one cell at a time I'd suggest using the Intersect method; especially if it's a change_event. This will reduce overhead of looping, if it's not needed that is.

mdmackillop
03-01-2005, 05:14 PM
Thanks Zack, code amended accordingly!

Blinkin
03-02-2005, 08:03 AM
Thanks for all your help. It works great. I do, however, have one more question. How do I enter values in empty cells? It's like the cells are protected. I enter something and hit enter and the cell stays empty. This only happens in the cells that are in the TimeCell range. Do I need to temporarily turn off the VBA code? If so, what's the quickest/easiest way to do that?

mdmackillop
03-02-2005, 10:28 AM
Here's a little piece of code to do that. To let you know what's happening, create a userform with a caption such as "Enable events restored". Create a button or shortcut to run the Toggle code.


Sub ToggleEvents()
If Application.EnableEvents = False Then
UserForm1.Show False
Application.EnableEvents = True
Application.OnTime Now + TimeValue("00:00:01"), "CloseForm"
Else
Application.EnableEvents = False
End If
End Sub
Sub CloseForm()
Unload UserForm1
End Sub

Blinkin
03-03-2005, 11:43 AM
This is working pretty cool. However, I get an error saying:

The macro "<filePathHere>.xls"'!CloseForm' cannot be found.

and the userform does not close. I tried changing the call (thinking it needed a syntax change), but couldn't get anywhere.

Any ideas?

mdmackillop
03-03-2005, 11:52 AM
Can you post a copy of your spreadsheet? (use the Go Advanced button to do this)

Blinkin
03-03-2005, 12:34 PM
Attached.

mdmackillop
03-03-2005, 12:50 PM
Try moving the closeform macro from the worksheet module to a standard module

Blinkin
03-03-2005, 02:15 PM
Works like a charm. Thanks so much.