Date error message (VBA)

[bruty]

I created a spreadsheet using Office 2003 on an XP machine that on open looks at todays date and checks it against a shelflife date using this code:

TodaysDate = Date

When a colleague using Excel 2000 on a Windows NT (or 2000, not 100% sure at the moment) tries to open up the spreadsheet they get an error saying:

Compile Error:
Can't find project or library

and it highlights the word Date (the one after the equals sign). I thought this was a standard VBA keyword, so I don't understand why this is causing a problem. Can anyone shed some light for me??

 

[Norie]

I suggest you have a look under Tools>References... in the VBE on your colleague's machine.

Anything MISSING?

 

[bruty]

I wouldn't know what to look for to be honest. Also the colleague it based in a completely different part of the country so it's not as easy as just nipping around the corner unfortunately.

Do you know what should be included on Excel 2000 to get this to work - I may be able to talk them through it on the phone.

 

[Norie]

Why don't you just get them to do this?

1 Open the VBE.(Alt+F11)

2 Goto Tools>References...

3 Ask them what they see.

This is a list of all the object libraries referenced and if anying are marked as MISSING then that could be the cause of the problem.

 

[bruty]

OK. Will give it a try - cheers.

 

[bruty]

Is there anyway around this that I can put into my code?

This is in a spreadsheet used by several users throughout the business and I am unable to go to each one, so I'm hoping there is something to keep this working.

 

[Norie]

Bruty

Was there a problem with a reference?

If so you might be able to use VBA.Date.

But the problem with that is that you would need to do that for every VBA function.

It would be far better to actually fix the problem.

 

[bruty]

There was on a couple of machines I checked (I think, it was a while ago now, but the problems suddenly resurfaced).

How would I fix this problem? Would it be a case of checking on each machine, or is there an easy fix? The problem is we have offices all around the country and I don't have a clue who will be using the spreadsheet and who wont.

 

[Norie]

bruty

It's probably not really a problem with the actual spreadsheet it's probably more to do with the actual setup on each computer.

That said you've only posted one line of code and you don't say if you are using anything non-standard in the code. eg a calendar control

If you are using, or have used while developing the spreadsheet, anything non-standard that could be one source of the problem with references.

Another could be if you were automating other applications in the code but the spreadsheet was being distributed to people with different versions of those applications.

 

[bruty]

Here's the full code:

Private Sub Workbook_Open()

Application.ScreenUpdating = True
'Unhide sheets if macros are enabled
    Me.Sheets("Main sheet").Visible = True
    Me.Sheets("Main sheet").Visible = True
    Me.Sheets("Contact details").Visible = True
    Me.Sheets("Vacancy details").Visible = True
    Me.Sheets("Additional information").Visible = True
    Me.Sheets("Summary").Visible = True
    Me.Sheets("ERROR").Visible = False

'Select Main Sheet
Me.Sheets("Main sheet").Select

'Any message to be displayed at startup
'OpenMessage = MsgBox("This is a trial version of the RCR. It will expire on Sunday 1st July. After this you will need to download a live version from HR Help", vbOKOnly, "TRIAL RCR")

Dim CheckDate As Date, TodaysDate As Date
'Set scroll areas
    Me.Sheets("Main sheet").Select
    Me.Sheets("Main sheet").ScrollArea = "a1:i56"
    Me.Sheets("Contact details").ScrollArea = "a1:i20"
    Me.Sheets("Vacancy details").ScrollArea = "a1:j56"
    Me.Sheets("Additional information").ScrollArea = "a1:i52"
    Me.Sheets("Summary").ScrollArea = "a1:i78"
'Shelf Life Section
    CheckDate = "30/06/2008" 'This is the date for the Shelf Life. Change this for each new version released.
    TodaysDate = Date
    'If TodaysDate = CheckDate + 20 Then 'RCR Expires in 10 days
    'MsgBox ("This is an old version of the RCR form and will expire in 10 days. Please download the new version from the Intranet")
    'ElseIf TodaysDate = CheckDate + 21 Then 'RCR Expires in 9 days
    'MsgBox ("This is an old version of the RCR form and will expire in 9 days. Please download the new version from the Intranet")
    'ElseIf TodaysDate = CheckDate + 22 Then 'RCR Expires in 8 days
    'MsgBox ("This is an old version of the RCR form and will expire in 8 days. Please download the new version from the Intranet")
    'ElseIf TodaysDate = CheckDate + 23 Then 'RCR Expires in 7 days
    'MsgBox ("This is an old version of the RCR form and will expire in 7 days. Please download the new version from the Intranet")
    'ElseIf TodaysDate = CheckDate + 24 Then 'RCR Expires in 6 days
    'MsgBox ("This is an old version of the RCR form and will expire in 6 days. Please download the new version from the Intranet")
    If TodaysDate = CheckDate - 5 Then 'RCR Expires in 5 days
    MsgBox ("This version of the RCR form will expire in 5 days.")
    ElseIf TodaysDate = CheckDate - 4 Then 'RCR Expires in 4 days
    MsgBox ("This version of the RCR form will expire in 4 days.")
    ElseIf TodaysDate = CheckDate - 3 Then 'RCR Expires in 3 days
    MsgBox ("This version of the RCR form will expire in 3 days.")
    ElseIf TodaysDate = CheckDate - 2 Then 'RCR Expires in 2 days
    MsgBox ("This version of the RCR form will expire in 2 days.")
    ElseIf TodaysDate = CheckDate - 1 Then 'RCR Expires in 1 day
    MsgBox ("This version of the RCR form will expire in 1 days.")
    ElseIf TodaysDate = CheckDate Then  'RCR Expires today
    MsgBox ("This version of the RCR form expires today.")
    ElseIf TodaysDate > CheckDate Then  'RCR Expired
    MsgBox ("This version of the RCR form has expired.")
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Else
    End If
'Disable cut, copy & paste
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", "Error"
Application.OnKey "^v", "Error"
Application.OnKey "^x", "Error"
Application.OnKey "+{DEL}", "Error"
Application.OnKey "^{DEL}", "Error"
Application.OnKey "+{INSERT}", "Error"
Application.OnKey "^{INSERT}", "Error"
Application.CellDragAndDrop = False
'Application.OnDoubleClick = "Error"

'Select Main Sheet
Me.Sheets("Main sheet").Select
        
End Sub

I don't know if there is anything non-standard there as I just cobble it all together and steal bits from here, there and everywhere.