2000 VBA vs. 2010 VBA

[spectraflame]

I have used the following code for years in MS Excel 2000 on the Worksheet. Now that I have opened the sheet in 2010 and they tried to pen it again in 2000, I get an "Compile Error - Cannot find project or library". Is there something that I need to change in the code in order to get it to function correctly? I really do not care much for the new version. Guess i am getting too old to change or learn something new.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
' Chip Pearson
' http://www.cpearson.com/excel/DateTimeEntry.htm
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A11:A10000")) Is Nothing Then
Else
For Each c In Intersect(Target, Range("A11:A10000"))
If c.Offset(0, 1).Value = "" Then
c.Offset(0, 1).Value = Now()
c.Offset(0, 2).Value = Format(Now, "DDDD")
Else
c.Offset(0, 1).ClearContents
c.Offset(0, 2).ClearContents
End If
Next c
End If
If Application.Intersect(Target, Range("D11:F10000")) 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


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address

Case Range("A6").Address

Target = Format(Now, "mm/dd/yyyy")

End Select
End Sub

 

[Norie]

Matthew

You might have a missing reference, which could be caused by the version change.

To check goto Tools>References... and check for anything marked MISSING.

If you find something you can simply uncheck it to get this code to work, but you should note what it is and check if the reference is needed anywhere else.

If that's the case you might need to change the reference to refer to the 2010 version of whatever it it.

 

[spectraflame]

It appears that the Microsoft 14.0 object library is missing when the sheet is opened from a different version other than 2010. Guess this is something that is added by 2010. Is there a way to prevent this but still allow both my 2010 users and my 2000 users to use the same workbook until I can get them all upgraded?

 

[Norie]

Matthew

I don't quite follow, I though it didn't work in 2010 but you seem to be saying it's the other way round, not working in earlier versions.

The version of the library you mention is the one Excel 2010 uses.

Have there been any changes to the file?

PS Are you using 64-bit 2010?

 

[spectraflame]

The sheet worked great with Excel 2000. We upgraded one user in the office who uses this sheet to Office 2010 and the sheet worked without issue. Once it was opened in 2010 and saved and a user using 2000 attempted to modify it, that is when the error appeared. I am hoping that this issue goes away once everyone is upgraded to Office 2010. We are using the 32 bit version.

 

[Norie]

Matthew

Sounds like somehow the file's been 'converted' to a 2010 file.

Try opening it in 2010 then saving it as an Excel 97-2003 Workbook (*.xls).

Then try it agiain in both 2000 and 2010 and any version inbetweens, but don't save it as an xlsm even if prompted, which you shouldn't be anyway.

 

[spectraflame]

Found out that the whole issue was due to a missing Object Library. When the document was saved in Excel 2010, it was using an Object Library that Excel 2000 did not have available. The quick fix was to just upgrade the other workstations that use this file to Office 2010.

Thank you for your help.