2003 code not working in 2007

[averilp]

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

 

[Richard Schollar]

Hi Averil

What error do you get with the first code (ie at the Format function)?

 

[averilp]

That is really bizarre... just tried it again and it doesn't seem to break.

I can't remember the exact error message, but it wasn't actually "breaking" i.e. wasn't highlighting in yellow, rather the word Format was highlighted normally (blue).

Other odd things I have noticed... On files I have opened in compatibility mode on 2007 computers then opened again on mine (2003) my weeknum formula comes up with #Name? even though I have the analysis toolpak... I also can't get the calendar control working on the 2007 machines.

I think that this error I'm getting now on the 2007 machines is due to the calendar control. "Could not load an object because it is not available on this machine". When I go into the references on the 2007 machines, the Microsoft Calendar Control 11.0 is MISSING.

Thanks in advance for any light shed on this matter.

Cheers,
Averil