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

[Jake Blackmore]

Hello all,

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

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

 

[Jake Blackmore]

1. Visual Basic For Applications
2. Microsoft Excel 15.0 Object Library
3. OLE Automation
4. Microsoft Office 15.0 Object Library
5. Microsoft Forms 2.0 Object Library
6. Microsoft Outlook 15.0 Object Library

(in that order or Priority)

Late binding Emailer code:

Option Explicit

Public Sub Mailer()
    Dim OutApp      As Object
    Dim OutMail     As Object
    Dim rngCell     As Excel.Range
    Dim strEmail    As String
    Dim strFilename As String
    Dim strFullname As String
    Dim lngCol      As Long
    Dim strFolder   As String
    Dim strError    As String

    Call MsgBox(Prompt:="Select the folder where the files to be distributed have been saved", Buttons:=vbOKOnly + vbInformation, Title:="Files to be distributed")
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder for files to be distributed"
        If .Show Then
            strFolder = .SelectedItems(1)
        End If
    End With
    
    If Len(strFolder) Then
        On Error Resume Next
            Set OutApp = GetObject(, "Outlook.Application")
        On Error GoTo 0
        If OutApp Is Nothing Then
            Set OutApp = CreateObject("Outlook.Application")
        End If

        On Error Resume Next
            lngCol = Application.Match("GROUP FILE EMAIL", shtTeams.Rows(1), 0)
        On Error GoTo 0
        
        If lngCol > 0 Then
            With shtTeams
                For Each rngCell In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
                    If Application.CountIf(.Range("A2:A" & rngCell.Row), rngCell.Value) = 1 Then 'it is 1st itteration in the expanding range
                        strEmail = .Cells(rngCell.Row, lngCol).Value
                        strFullname = strFolder & "\Willis Finex - " & rngCell.Value & ".xlsb"
                        strFilename = Dir$(strFullname)
                        If Len(strFilename) Then
                            Set OutMail = OutApp.CreateItem(0)
        
                            With OutMail
                                .To = strEmail
                                .Subject = strFilename
                                .Body = "Hi," & Chr(10) & "Please see attached the latest Willis Finex file for you." 'Could refer to them by name
                                Call .Attachments.Add(strFullname)
                                .display
                                'Call Application.Wait(Now + TimeSerial(0, 0, 5))
                                '.Send
                            End With
                        End If
                    End If
                Next rngCell
            End With
        Else
            strError = "Please make sure the column 'GROUP FILE EMAIL' exists and is labelled as such!"
        End If
    Else
        strError = "Please choose a valid folder!"
    End If

    If Len(strError) Then
        Call MsgBox(Prompt:=strError, Buttons:=vbExclamation + vbOKOnly, Title:="Error!")
    End If

    Set OutMail = Nothing
    Set OutApp = Nothing
    Set rngCell = Nothing
End Sub