A
Auric__
I have this sub:
Sub fixTimes(what As Range)
Dim cell As Range
For Each cell In what
If IsNumeric(cell.Formula) Then
Select Case cell.Formula
Case 0 To 2359
cell.Value = (CLng(cell.Formula) \ 100) & ":" & _
(CLng(cell.Formula) Mod 100)
End Select
End If
Next
End Sub
....which is called from Workbook_SheetChange in the ThisWorkbook object:
Dim cell As Range
For Each cell In Target
Select Case cell.Column
Case 2 To 8
fixTimes cell
[etc.]
(It's important to note that columns B:H have their formatting set
automagically by Excel after the first few rows.)
The sub lets me enter times as a flat number, e.g. 600 for 6:00 am. My
problem is, if I enter data that is already properly formatted as a time
(which can happen via copy/paste or delete/undo), the sub runs against the
existing time using the existing formula (which is 0.25 for 6:00 am). This
changes the entered time to something very close to midnight... not what I
want.
I tried checking the .Text value for ":", but since this is already set
(after those first few rows) before my sub runs (e.g. entering "600" sets
the cell to "8/22/1901 12:00:00 AM"), it always returns True, and my sub
never gets to make the appropriate changes.
So, anyone have any ideas how to make this DWIM? (WIM = if I enter a number,
make it a time; if I enter a time, do nothing.)
Sub fixTimes(what As Range)
Dim cell As Range
For Each cell In what
If IsNumeric(cell.Formula) Then
Select Case cell.Formula
Case 0 To 2359
cell.Value = (CLng(cell.Formula) \ 100) & ":" & _
(CLng(cell.Formula) Mod 100)
End Select
End If
Next
End Sub
....which is called from Workbook_SheetChange in the ThisWorkbook object:
Dim cell As Range
For Each cell In Target
Select Case cell.Column
Case 2 To 8
fixTimes cell
[etc.]
(It's important to note that columns B:H have their formatting set
automagically by Excel after the first few rows.)
The sub lets me enter times as a flat number, e.g. 600 for 6:00 am. My
problem is, if I enter data that is already properly formatted as a time
(which can happen via copy/paste or delete/undo), the sub runs against the
existing time using the existing formula (which is 0.25 for 6:00 am). This
changes the entered time to something very close to midnight... not what I
want.
I tried checking the .Text value for ":", but since this is already set
(after those first few rows) before my sub runs (e.g. entering "600" sets
the cell to "8/22/1901 12:00:00 AM"), it always returns True, and my sub
never gets to make the appropriate changes.
So, anyone have any ideas how to make this DWIM? (WIM = if I enter a number,
make it a time; if I enter a time, do nothing.)