Excel - Automation Error - Please Help

[mattygriff]

I have an Excel workbook which, when opened, generates a new payment number. A macro then checks the Payment List sheet to see if the list is full. If it is, a new line is inserted below the last entry.

Option Explicit

Dim numPayments As Long
Dim numPaymentsMaxLength As Long

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        numPayments = Sheet2.Range("PAYMENTS_LIST").Rows.Count
        numPaymentsMaxLength = Sheet2.Range("MAX_PAYMENT_LIST").Rows.Count
    
        If numPayments >= numPaymentsMaxLength Then
        
            Sheet2.Unprotect
            Rows(11 + numPayments).Insert Shift:=xlDown
            Sheet2.Protect
        
        End If
        
    End If
        
    
End Sub

I'd be grateful for any help you might have.

 

[mattygriff]

Just realised that I haven't explained what my problem is!!!

When the macro runs, I get the following error :

"Run-time error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients."

Thanks in advance.

 

[PHV]

And which line is highlighted when in debug mode at the time the error raises ?
Anyway, I'd change this:
Rows(11 + numPayments).Insert Shift:=xlDown
with this:
Sheet2.Rows(11 + numPayments).Insert Shift:=xlDown

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

I've never seen that error before as I recollect.

BUT, it seems that you are going thru handstands and hoops in order to accomplish a simple thing. If you were to use an Excel LIST (2003 and earlier) or TABLE (2007 and greater) extending a table is simply navigating to the next available row and entering data, which basically does what your posted procedure is intended to do.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mattygriff]

Thanks for replying, guys.

PHV - it's the insertion line that's highlighted by the debugger. I'll try your change and see if that makes any difference.

Skip - aside from having to cater for different versions of Excel, I also need the list of payments to be (in theory) infinitely extendible. The "PAYMENTS_LIST" range is dynamic based upon the number of entries. Not sure whether any of this makes any difference to what you say tho!

 

[mattygriff]

Hi guys - just a quick update.

Managed to solve the problem myself after some testing - basically the inserted line was falling outside the MAX_PAYMENT_LIST range so the test was always finding that the PAYMENTS_LIST range was longer resulting in an endless loop.

Not sure why it gave the error I reported but there you go.

Thanks for the replies.

 

[Gruuuu]

Lists and Tables are expandable (not infinitely thought, they are limited by the number of rows available).
Other than typing, you can paste or even enter values from a macro, and the list will auto-expand.

Really, just take a second to play around with it. It's a nice element that brings a lot to the table.