2003 code not working in 2007

averilp

Board Regular
Joined
Oct 30, 2006
Messages
59
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

averilp

Board Regular
Joined
Oct 30, 2006
Messages
59
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,054
Messages
5,599,533
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top