VBA - Worksheet Deactivation Event - Compile Error: Can't find Project or Library

Jake Blackmore

Board Regular
Joined
Nov 24, 2014
Messages
200
Hello all,

I've created the following macro to run on a worksheet deactivation event in a file that is given to my client:
Code:
Option Explicit

Private Sub Worksheet_Deactivate()
    Dim lngResponse As Long

    If Not Me.Range("rng_OriginalCurrency").Value = Me.Range("rng_CurrencyOption").Value Or Not Me.Range("rng_OriginalTeam").Value = Me.Range("str_ActiveTeam").Value Then
        Me.Range("rng_OriginalCurrency").Value = Me.Range("rng_CurrencyOption")
        lngResponse = MsgBox(Prompt:="Are you sure you wish to refresh the CCY Cummary?" & Chr(10) & "Please note all changes made to CCY Summary will be overwritten", Buttons:=vbYesNo + vbQuestion, Title:="Refresh CCY Summary?")
        If lngResponse = 6 Then
            Call ApplyPvtFilters
        End If
    End If
End Sub

They are running Excel 2010 and a colleague using the same network also struggled with the issue. The issues seems to stumble on being unable to find the deactivation event in the library.

Really would appreciate any guidance given,
Thank you,
Jake
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Excel events do not depend on references, but your code almost certainly has a missing reference on the afflicted machines. You'll probably need access to one of the machines to figure out which one is not available to them.

Which version did you develop on, and what references are checked?
 
Upvote 0
Hi Rory - I'm on 2013 and they are on 2010. I haven't altered the reference selection from the default selection as it's only recently I've learned of their existence haha. Is there a way to tick / enable references via VBA should I find one is missing?
 
Upvote 0
If it's missing you don't want to tick it - it's already checked, but won't work because it isn't available! ;)

Can you ask one of the affected users to look at the references and tell you which is/are prefixed with 'MISSING:' in the list?
 
Upvote 0
Ok so I've manage to find that their references use Microsoft Excel 14.0 Object Library where as I use Microsoft Excel 15.0 Object Library (Same with Office). I can also see it's stumbled on Chr(10)...

...any help guidance would be much appreciated,
Jake
 
Upvote 0
That can't cause this problem. It would be a checked reference that is shown as 'MISSING: ...' on their computer. The Excel library will automatically adjust and that won't cause an issue. References to other Office programs will cause a problem, as might things like Windows Common Controls.

It is possible to largely avoid this kind of issue by specifying the library for all functions you use explicitly (e.g. use VBA.Chr rather than just Chr) but it's easier to correct the actual reference issue since any code that does need the missing reference will fail.
 
Last edited:
Upvote 0
I'm all up for being Explicit - I used VBA.Chr and it worked thank you.

I'm still concerned as like you have sad I haven't fixed the actual reference issue. I recently wrote some code in the file referencing Outlook as an application but then I destroy the declared object at the end of the code so not sure why but perhaps something (Like the reference) is still lingering in memory?...
 
Upvote 0
If you set a reference to the Outlook object library, that would cause the problem - or did you late bind?
 
Upvote 0
What references do you have selected in the project?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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
Back
Top