method open of object workbooks failed

[dpaton05]

While working on a spreadsheet at work, I was getting the error "method open of object workbooks failed", but when I got home, the same file was not giving me any errors. Any ideas why?

 

[dpaton05]

I replaced the line of code and if stopped deleting lines altogether.

This is what I now have:

Private Sub cmdDelSelectRow_Click()

ActiveSheet.Unprotect Password:="npssadmin"
    Dim rng As Range
    
    On Error Resume Next
    With Selection.Cells(1)
        Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
        On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "Please select a cell within a row that you want to delete.", vbCritical
        Else
            If rng.HasFormula = False Then rng.Delete
        End If
    End With
    
'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"

Application.EnableEvents = True
'ActiveSheet.Protect Password:="npssadmin"
End Sub

Is there something I left out.

 

[Michael M]

Do the rows getting deleted contain formulas ??
If any cells on that rows hold a formula, the code won't remove that row !!

 

[dpaton05]

Every row contains cells that have formulas at the end of the table. I wanted the selected rows to be deleted, but if there is only one row left in the table and the button is clicked on the delete selected row when the last row is selected, I want the contents deleted, which is in columns up to H and everything else left. All the rest are formulas.

 

[dpaton05]

I think I am going to try and convert it all to use a range instead of tables. I am going to need some help on the code for all of the buttons. I don't know where to start. I have poured months of effort at work into this and doing so will mean that all of the work I have put in will be wasted. This will also make my supervisor very annoyed. I have no idea what to do Michael.

 

[Michael M]

Yeah, you're probably too far down the path now to really change !!
You could create a copy and start to convert it in your spare time.
In the meantime try

UNTESTED

Private Sub cmdDelSelectRow_Click()

ActiveSheet.Unprotect Password:="npssadmin"
    Dim rng As Range
    
    On Error Resume Next
    With Selection.Cells(1)
        Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
        On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "Please select a cell within a row that you want to delete.", vbCritical
        End If
        If rng.Offset(1).HasFormula = True Then
        rng.Delete
        Else
            Exit Sub
        End If
    End With
    
'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"

Application.EnableEvents = True
'ActiveSheet.Protect Password:="npssadmin"
End Sub

 

[dpaton05]

That didn't work Michael, but I thought of an idea. What if there was some code to delete a row but if it is the last row left or row 5, insert the formulas back into the cells. The header row is row4 and the data starts in row 5. For every record in the table, the date is in column A and..

• there is a formula in column Z

  • =TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "mmmm")

• there is a formula in column AA

  • =TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "yyyy")

• there is a formula in column AD

  • =CONCATENATE(AA5+1," - ",AA5+2)

• there is a formula in column AG

  • =IF(MONTH(A5)<7,YEAR(A5)-1,YEAR(A5))

• there is a formula in column AH

  • =IF(MONTH(A5)<7,YEAR(A5),YEAR(A5)+1)

• there is a formula in column AI

  • =CONCATENATE(AG5," - ",AH5)

• there is a formula in column AJ

  • =CONCATENATE(AI5," ","NPSS Work Allocation Sheet.xlsm")

• there is a formula in column AK

  • =CONCATENATE(AI5," ","Internal Work Allocation Sheet.xlsm")

Thanks Michael,
Dave

 

[dpaton05]

Yeah, you're probably too far down the path now to really change !!

But how do I continue if I can't get it to work?

 

[dpaton05]

If I start again with multiple ranges instead of tables, can you give me some tips to give me a little head start on the coding please?

 

[Norie]

Perhaps start a new topic for this new issue.