Date error message (VBA)

bruty

Active Member
Joined
Jul 25, 2007
Messages
445
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??
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
I suggest you have a look under Tools>References... in the VBE on your colleague's machine.

Anything MISSING?
 

bruty

Active Member
Joined
Jul 25, 2007
Messages
445
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 25, 2007
Messages
445

ADVERTISEMENT

OK. Will give it a try - cheers.
 

bruty

Active Member
Joined
Jul 25, 2007
Messages
445
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jul 25, 2007
Messages
445
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 25, 2007
Messages
445
Here's the full code:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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