RE: Time with VBA code

Thank you. The first part works well.
~If Application.Intersect(Target, Range("A1:A200")) Is Nothing and _
Application.Intersect(Target, Range("E1:E200")) Is Nothing then
Exit
end if~

I'm not sure where to put the second part. ~Range("E" & Target.Row) = TimeStr~

Would you know anything about my other dilemas?

How can i display 12:01 midnight (00:01) ?



"Joel" wrote:

To use more than one column use and AND statement here

If Application.Intersect(Target, Range("A1:A200")) Is Nothing and _
Application.Intersect(Target, Range("E1:E200")) Is Nothing then
Exit
end if


To put data in another column use the following

Range("E" & Target.Row) = TimeStr


"Dale G" wrote:

I’m having some trouble with this time VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String
On Error GoTo EndMacr

If Application.Intersect(Target,
Range("D3:D200")) Is Nothing Then
Exit
End If
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

With Target
If .HasFormula = False Then

If .Value >= 1 Then

Select Case Len(.Value)

Case 1 ' e.g., 1 = 01:00 AM

TimeStr = Left(.Value, 2)& ":00"

Case 2 ' e.g., 12 = 12:00 AM

TimeStr = .Value & ":00"

Case 3 ' e.g., 123 = 1:23 AM

TimeStr = Left(.Value, 1) & ":" & _


Right(.Value, 2)

Case 4 ' e.g., 1234 = 12:34 AM

TimeStr = Left(.Value, 2) & ":" & _

Right(.Value, 2)

Case Else

Err.Raise 0
End Select

.Value = TimeValue(TimeStr)
End If

.NumberFormat = "h:mm;@"
End If
End With

Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time. Please use figures only for the
time e.g. 1030" Application.EnableEvents = True

End Sub

First is there a way to use this code in two columns without using the
entire range like A1:E200
Let’s say A1:A200 & E1:E200?
Also, I use the code to enter time in column E (Actual time) & column D has
a fixed time & column F has a (= the difference set up). All of this is done
in military time. The problem is; when the fixed time in column D is 23:55 &
I enter the actual time of 24:10 it does not work. The difference column will
show 5 minutes late, & the actual time will show 0:00. Any solution?
Lastly is it possible to use this code as a module so I would not have to
have the entire code attached to each sheet?

Any help is appreciated.

.

Relevant Pages

  • RE: Time with VBA code
    ... "Dale G" wrote: ... Private Sub Worksheet_Change ... a fixed time & column F has a (= the difference set up). ... when the fixed time in column D is 23:55 & ...
    (microsoft.public.excel.misc)
  • RE: Time with VBA code
    ... Private Sub Worksheet_Change ... MsgBox "You did not enter a valid time. ... a fixed time & column F has a (= the difference set up). ... when the fixed time in column D is 23:55 & ...
    (microsoft.public.excel.misc)
  • RE: Time with VBA code
    ... I don't know exactly what your input data looks like so it is hard to give a ... Private Sub Worksheet_Change ... a fixed time & column F has a (= the difference set up). ... when the fixed time in column D is 23:55 & ...
    (microsoft.public.excel.misc)
  • Time with VBA code
    ... Private Sub Worksheet_Change ... On Error GoTo EndMacr ... a fixed time & column F has a (= the difference set up). ... when the fixed time in column D is 23:55 & ...
    (microsoft.public.excel.misc)
  • RE: Best way using Macros
    ... Hi Joel, I have set my security to low, it was on medium. ... Private Sub App_WorkbookOpen(ByVal Wb As Workbook) ... Since you probably have outlook open all the time ...
    (microsoft.public.excel.programming)