How can I get this to work

[David Fisher]

I asked this question before with no reply so I will ask again in
a different format.

In a templet I want to us Chip Pearson time code for Times, Quick Entry

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A500")) Is Nothing Then
Exit Sub

for the following columns only
F,G,H,I,J,O,P,Q,S,C,Y,AB,AE,AH,AK,AN,AQ,AR,AW,AZ,BC,BF,BI,BL,BO,BR,BU,BY,
CG,CJ,CM,CP,CS,CV,CY,DB,DE,DH,DK,DN,DQ,DT,DW,DZ,EC,EF,EI,EL,EO,ER,EU,EX,
FA,FD,FG,FJ,FM,FP,FT,FU,FV,GA,GB,GC
but cannot come up with away of doing it.

I have try (Target, Range("F5:J100","O5:Q500",S5:S500",ETC))

I have try (Target, Range("F5:J500,O5:Q500,S5:S500,ETC"))

I have try (Target, Range("F5:F500","G5:G500","H5:H500","ETC"))

I have also try (Target, Range("MyTimeCol",Row ("5:500"))With no luck.

The problem is, this templet will be change when added to various
workbooks as some of the cols will be remove and other columns will
be added.

The other columns will contain data not related to time.

If I do this
If Application.Intersect(Target, Range("F5:GC500")) Is Nothing Then

all the other columns cannot be change to accept data as 1, 12 or yes.

Is there away I can these cols to work for Quick Entry?

 

[Matthew Connor]

BTW, the above line is the correct syntax. I imagine a typo crept in
somewhere.

Matthew

 

[David Fisher]

I found this works:
If Application.Intersect(Target, Range("TimeColumns")) Is Nothing Then
Exit Sub
End If

where you have pre-defined a named range called "TimeColumns" in the
worksheet. You can then edit the named range as different
workbooks/worksheets require different columns.

Hope this helps,

Matthew

I use "MyTimeCols"

When I enter 104545 in any col label "MyTimeCols" I get 00:00:00 that is
underline and the date changes to 3/25/2186 in that cell.

Also, it was a typo error in my post.

 

[Matthew Connor]

Matthew Connor wrote:




I use "MyTimeCols"

When I enter 104545 in any col label "MyTimeCols" I get 00:00:00 that is
underline and the date changes to 3/25/2186 in that cell.

Also, it was a typo error in my post.

You'll need to post the entire Private Sub Worksheet_Change routine
for us to see what is happening. It sounds like some error (not what's
in Chip's Quick Entry code) has crept in.

Matthew

 

[David Fisher]

You'll need to post the entire Private Sub Worksheet_Change routine
for us to see what is happening. It sounds like some error (not what's
in Chip's Quick Entry code) has crept in.

Matthew

Other than change "MyTimeCols" the code is the same as on the website.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("MyTimeCols")) Is Nothing
Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

 

[Matthew Connor]

Other than change "MyTimeCols" the code is the same as on the website.

<code snipped>
The problem you're having is that the cells you are entering are
already formatted to display a date not a time. You can fix this by
selecting the cells and going to Format |Cells |Number(tab) and select
General or you choice of Time formats.

Good luck,

Matthew