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

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

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.