PDA

View Full Version : Column headers throws errors



Aussiebear
08-05-2013, 05:10 PM
The following code allows me to quickly enter time by converting the four digits to military time within Columns A & B. In testing I can enter any cell in Columns A and B and it works. However if I enter a column Header if causes the code to inaccurately enter values. Should I amend the code to include a row.count to overcome this issue.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColumn As Integer
Dim OldInput As String
Dim NewInput As String
Dim rRow As Integer
rColumn = Target.Column
If rColumn < 3 Then
OldInput = Target.Value
If OldInput > 1 Then
NewInput = Left(OldInput, Len(OldInput) - 2) & ":" & Right(OldInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub

GTO
08-05-2013, 05:28 PM
The following code allows me to quickly enter time by converting the four digits to military time within Columns A & B. In testing I can enter any cell in Columns A and B and it works. However if I enter a column Header if causes the code to inaccurately enter values....

Hi Ted :hi:

I was not sure what you mean by "...enter a column header..." ?

I entered "1500" in Row 1 and it changes to 15:00 as expected. What am I missing?

By the way, I would suggest adding If Target.Count = 1 Then... I only tack that in, as if you were to (for instance) delete all the cells on the sheet (guess who just tried), you get an 'out of memory' error.

Mark

Aussiebear
08-05-2013, 05:41 PM
Column header such as Start, Finish etc. I guess anything other than 4 digits

Aussiebear
08-05-2013, 05:45 PM
By the way, I would suggest adding If Target.Count = 1 Then...
So adding

If Target.Count > 1 then
Exit Sub

Should take care of the accidental selection of multiple cells

Aussiebear
08-05-2013, 06:09 PM
or should that have been

If target.Cells.Count > 1 Then
Exit sub

SamT
08-05-2013, 06:45 PM
Mix and Match

If Target.Count > 1 Then Exit Sub
IF Not Target IsNumeric Then Exit Sub
IF Len(Trim(Target)) <> 4 Then Exit Sub
'That one is tricky if you use 900 for 0900
If (Target > 2400) Or (Target < 1) Then Exit Sub

And how do you deal with 12:59 AM ((Target = 59)

Why not just enter the time as mil-time? IE; 13:30 or 0:59, Both of which Excel recognizes as Mil-time and stores the value as Windows Serial time values?

Aussiebear
08-05-2013, 06:54 PM
Just being lazy rather than enter the colon between the digits

12:59 is simply 1259 <Enter>

GTO
08-05-2013, 07:40 PM
Hi my friend,

Here is what I came up with:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Column < 3 Then
With Target
If .Value Like "#" Or .Value Like "##" Or .Value Like "####" Then
If .Value < 2400 Then
Application.EnableEvents = False
.Value = TimeSerial(Int(.Value / 100), (.Value Mod 100), 0)
Application.EnableEvents = True
Target.NumberFormat = "hh:mm"
End If
End If
End With
End If

End Sub

You might want to take a gander at one of our KB entries here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=75)

Hope that helps?

Mark

snb
08-06-2013, 08:33 AM
or

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Count = 1 * Target.Column < 3 * IsDate(Format(Target, ",00:00")) Then Target = Format(Target.Value, "00:00")

Application.EnableEvents = True
End Sub

Paul_Hossler
08-06-2013, 01:10 PM
My 2 cents

If you want to handle multiple cells being pasted in

If you want to restrict to a certain column or columns, just add the ... And rCell.Column = 3 ... inside the For

Did you want to leave these as numbers so that you could use them in formulas? This just displays them as hh:mm





Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
Dim rCell As Range
Application.EnableEvents = False
For Each rCell In Target.Cells
' If rCell.Row > 1 And rCell.Column = 3 Then
If rCell.Row > 1 Then
If IsNumeric(rCell.Value) Then
If rCell.Value = 0 Then
rCell.ClearContents
Else
s = Format(rCell.Value, "0000")
If Len(s) = 4 Then
rCell.Value = "'" & Format(Left(s, 2), "00") & ":" & Format(Right(s, 2), "00")
End If
End If

End If
End If
Next
Application.EnableEvents = True
End Sub




Paul

snb
08-07-2013, 01:15 AM
I have to amend my previous suggestion:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If (Target.Count = 1) * (Target.Column < 3) * IsDate(Format(Target, "00:00")) Then Target = Format(Target.Value, "00:00")

Application.EnableEvents = True
End Sub