Please help - The object invoked has disconnected from its clients. (1 Viewer)

[Kenln]

I am using an Excel app that is being used in Office 2005 and 2007.

It works fine in Office 05 but 09 it crashed with

The object invoked has disconnected from its clients.

I found this http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832 which I thought would solve my problem but...

I guess I don't understand the problem enough.

Can someone PLEASE help me with the code??????

I know I have to get ride of some of the things like Application but replace them how????

If anyone could help I would REALLY appreciate it.

I just found this problem so I late on releasing it.

Thank you,

Option Explicit

Public iCurrentRow As Integer
Public iCurrentColumn As Integer
Public dblBudgetCosts As Double
Public dblOrigCostAtCmplt As Double
Public dblCostToDate As Double
Public dblCommitted As Double
Public dblBugetHours As Double
Public dblOrigHoursAtCmplt As Double
Public dblHoursToDate As Double

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBudgetAvailable As Boolean
    bBudgetAvailable = False
Dim bCTDAvailable As Boolean
    bCTDAvailable = False

    Select Case iCurrentColumn
        Case 5
            If Application.Cells(iCurrentRow, iCurrentColumn).Value < dblCostToDate + dblCommitted Then
                Application.Cells(iCurrentRow, iCurrentColumn).Value = dblCostToDate + dblCommitted
            End If
        Case 12
            If Application.Cells(iCurrentRow, iCurrentColumn).Value < dblHoursToDate Then
                Application.Cells(iCurrentRow, iCurrentColumn).Value = dblHoursToDate
            End If

            If dblBugetHours > 0 _
            And dblBudgetCosts > 0 _
            Then
                bBudgetAvailable = True
            End If

            If dblHoursToDate > 0 _
            And dblCostToDate > 0 _
            Then
                bCTDAvailable = True
            End If

            Application.Worksheets(1).Unprotect ("Password")

            If bCTDAvailable = True Then
                Application.Cells(iCurrentRow, 5).Value = (Application.Cells(iCurrentRow, iCurrentColumn).Value) * (dblCostToDate / dblHoursToDate)
            Else
                If bBudgetAvailable = True Then
                    Application.Cells(iCurrentRow, 5).Value = (Application.Cells(iCurrentRow, iCurrentColumn).Value) * (dblBudgetCosts / dblBugetHours)
                Else
                    Application.Cells(iCurrentRow, iCurrentColumn).Value = dblHoursToDate
                End If
            End If

            Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

    iCurrentRow = Application.ActiveCell.Row
    iCurrentColumn = Application.ActiveCell.Column

    dblBudgetCosts = Application.Cells.Cells(iCurrentRow, 3).Value
    dblOrigCostAtCmplt = Application.Cells(iCurrentRow, 4).Value
    dblCostToDate = Application.Cells(iCurrentRow, 7).Value
    dblCommitted = Application.Cells(iCurrentRow, 8).Value
    dblBugetHours = Application.Cells(iCurrentRow, 10).Value
    dblOrigHoursAtCmplt = Application.Cells(iCurrentRow, 12).Value
    dblHoursToDate = Application.Cells(iCurrentRow, 14).Value

    Select Case Application.ActiveCell.Column
        Case 5
            Select Case Left$(Application.Cells(iCurrentRow, 1).Value, 1)
                Case Is <> "4"
                    Application.Worksheets(1).Unprotect ("Password")
                    Application.ActiveCell.Locked = False
                    Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
                Case Else
                    Application.Worksheets(1).Unprotect ("Password")
                    Application.ActiveCell.Locked = True
                    Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
            End Select
        Case 12
            Select Case Left$(Application.Cells(iCurrentRow, 1).Value, 1)
                Case "4"
                    Application.Worksheets(1).Unprotect ("Password")
                    Application.ActiveCell.Locked = False
                    Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
                Case Else
                    Application.Worksheets(1).Unprotect ("Password")
                    Application.ActiveCell.Locked = True
                    Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
            End Select
    End Select

    If Application.Cells(iCurrentRow, 2).Value = "SUBTOTAL" Then
        Application.Worksheets(1).Unprotect ("Password")
        Application.ActiveCell.Locked = True
        Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
    End If

    If iCurrentRow < 8 Then
        Application.Worksheets(1).Unprotect ("Password")
        Application.ActiveCell.Locked = True
        Application.Worksheets(1).Protect ("Password"), AllowSorting:=True, AllowFiltering:=True
    End If

End Sub

 

[petehilljnr]

My initial thoughts would be replace it with to replace the Application with a sheet variable because that seems to be what you are referring to.

e.g.

Dim sht as worksheet
set sht = Sheets(1)  'or set sht = Sheets("mySheet") or set sht = Activesheet
 
... 
 
sht.unprotect ("Password")
sht.cells(iCurrentRow,iCurrentCol).Value = whatever

etc etc

 

[Kenln]

Thank you,

It took a bit of trail and error, but I finally got it.

There were actually two problems.

• Explicitly calling an object - which was my first post.
• Never ending loop - When I made a change to a cell it called the Change Event that made a change to a cell which called the Change Event, and so on and so on...

I finally got it.

Thanks again.