PDA

View Full Version : Solved: How to Detect a Time Entry



Cyberdude
01-29-2008, 02:50 PM
I have two cells ?A1? and ?A2?.
?A1? has a date-only value showing (format: M/D/YY ? no time displayed).
?A2? has a time-only value showing (format: H:MM:SS ? no date displayed).
I want the user to select either ?A1? or ?A2?, then click a button to invoke a macro, which will enter the current date into ?A1? and enter the current time into ?A2?.

Since I have 16 pairs of these cells in different places on the worksheet, and the user is allowed to select either the date OR the time cell, the macro needs to determine which of the two cells the user has selected in order to put a date in the upper of the two cells and a time in the lower one. If the user always selected the upper cell, then there would be no problem, but I can?t predict which of the two he will select.

I began with these statements:
If IsDate(Range(SelAddr)) = True _
Then
?. . . (logic to fill in the date and time)
Else
If IsTime(Range(SelAddr)) = True _
Then
?. . . move up one cell and fill in the date then the time
Else
?. . . (error handling logic since he didn?t select either cell)
The problem is that there doesn?t seem to be a function named IsTime.
My question is: how does one examine a cell to determine if it contains a time value? :dunno

Bob Phillips
01-29-2008, 03:00 PM
If you know they wilkl select good cells, you could use



With ActiveCell

If IsNumeric(.Value2) Then

If .Value2 > 1 Then

.Value = .Value + Time
Else

.Value = .Value + Date
End If
End If
End With

lenze
01-29-2008, 03:26 PM
How about a Before Double_Click Event?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$A$1", "$C$1", "$E$1" 'etc
Target = Date
Target.Offset(1, 0) = "" 'Optional line
Case "$A$2", "$C$2", "$E$2" 'etc
Target = Time
Target.Offset(-1, 0) = "" 'Optional line
End Select
Cancel = True
End Sub

lenze

Cyberdude
01-29-2008, 05:33 PM
I'll give both suggestions a royal try. Thanx for the suggestions, guys! :friends: