Hi there,
I'm wondering if anyone can help me. I'm trying to make my timesheets spreadsheet work for two users who have new computers that came with 2007. The code works for the users who have 2003.
I have the following code (courtesy of Dave Hawley at Ozgrid) to "mask" time entries when start and end times are entered:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Intersect(Target, Range("E3:K4")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
The error seems to be at the Format(.Value part.
Also seems to have a hissy fit with my calendar control code at the =Date part:
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
From what I've read on the forums, Microsoft got rid of some things e.g. application.filesearch but I haven't sen anything about Format or Date changing.
Thanks in advance.
Averil Pretty
I'm wondering if anyone can help me. I'm trying to make my timesheets spreadsheet work for two users who have new computers that came with 2007. The code works for the users who have 2003.
I have the following code (courtesy of Dave Hawley at Ozgrid) to "mask" time entries when start and end times are entered:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Intersect(Target, Range("E3:K4")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
The error seems to be at the Format(.Value part.
Also seems to have a hissy fit with my calendar control code at the =Date part:
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
From what I've read on the forums, Microsoft got rid of some things e.g. application.filesearch but I haven't sen anything about Format or Date changing.
Thanks in advance.
Averil Pretty