Application-Defined or Object-Defined error run-time error

[perco754]

I get this error while trying to run the code below. It stops right after the .Delete line i.e. on the .Add line. Funny is, after the delete the rng is still responding TRUE on rng.Validation.Value. I thought the value should be FALSE after a deletion?

(rng is a valid range validated in the immediate window where I can see its correct address etc. and the rList is also OK holding the address of the validation list, finally No the sheet is NOT protected)

With rng
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With

 

[perco754]

Answerd to Andrew, also tried to attach a url to a screen dump but failed using the "postcard" button.

Now I try again:

a href="http://www.geting.se/viewimage/image/316206-excel_erro.JPG" target="_blank"

Here's part of my code:

Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim thisNameRange As String
    Dim endRow As Integer, summaryRow As Integer
    Dim rList As String, destList As String
    Dim rng As Range, protectionRange As Range
    Dim tmpColumn As Integer
    Dim dev As Boolean 'for debugging on/off
    
    Application.ScreenUpdating = False
    
    tmpColumn = 50
    'MsgBox "Sheet " + sh.Name + " activated."
    'dev = True
    dev = False
    
    If Not objWB Is Nothing Then
        Set objWS = sh
    Else
        Module1.start False
    End If
    Set objWS = sh
    Module1.activeDepartment = objWS.name
    
    If Not dev Then
        If Module1.isDepartment(objWS.name) Then
            objWS.Unprotect passWrd
            Protection.UnprotectTheseCells objWS
            
            endRow = 3
            thisNameRange = "res" + objWS.name
        
            Set rng = objWS.Range(objWS.Cells(1, tmpColumn), objWS.Cells(1000, tmpColumn))
            rng.ClearContents
        
            objWB.Sheets("Own Resources").Range(thisNameRange).Copy objWS.Cells(endRow, tmpColumn)
            'MsgBox "Hej"
            Do While endRow < 1000 And StrComp(objWS.Cells(endRow, tmpColumn).Value, "") <> 0
                endRow = endRow + 1
            Loop
            objWS.Cells(endRow, tmpColumn) = "Konsult"
            objWS.Cells(endRow + 1, tmpColumn) = "Vakant"
            objWS.Cells(endRow + 2, tmpColumn) = "Inlån"
        
            rList = objWS.Range(objWS.Cells(3, tmpColumn), objWS.Cells(endRow + 2, tmpColumn)).Address
            endRow = endRow - 1
            Set rng = objWS.Cells.Find(what:="Summary", after:=objWS.Cells(1, 1), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=True, SearchFormat:=False)
            endRow = rng.Row - 1
            summaryRow = rng.Row
        
            Set rng = objWS.Range(objWS.Cells(5, 1), objWS.Cells(endRow, 1))
            With rng
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=" & rList
                    .ShowError = True
                End With
            End With
            Set rng = objWS.Range(objWS.Cells(5, 2), objWS.Cells(endRow, 2))
                With rng
                    With .Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=projNames"
                        .ShowError = True
                    End With
                End With
            'Module1.updateTotal objWS
            Module1.btnUpdateSummary
            'objWS.Cells(1, 1).Select
            
        End If
        
        If StrComp(objWS.name, "Total A", vbTextCompare) = 0 Or _
                StrComp(objWS.name, "Total AT", vbTextCompare) = 0 Or _
                StrComp(objWS.name, "Total AL", vbTextCompare) = 0 Then
            objWS.Unprotect passWrd
            Protection.UnprotectTheseCells objWS
            Module1.btnUpdateSummary
            
            Set protectionRange = Module1.GetProtectionRange(objWS)
            Protection.ProtectTheseCells protectionRange
            objWS.Protect Password:=passWrd, UserInterfaceOnly:=True
        End If
    End If
    
    Application.ScreenUpdating = True
    Module1.objExcel.ActiveWindow.ScrollRow = 1
End Sub

Perhaps you had better answer Andrew's question and, if it isn't too long, could we see the rest of the code?

 

[perco754]

Or do you mean the absolute value? The values of rList would differ for each sheet depending on the staff working at that specific department (one sheet for each dep)
e.g $AX$3:$AX$10

BR,
perco

the correct address to a range containing the string values of the validation list.

(I tried to upload an image showing a screen dump but the URL wasn't accepted??? )

 

[Andrew Poulsom]

What's the value of endRow whwn the procedure fails?

 

[Peter_SSs]

tried to attach a url to a screen dump but failed

See my signature block for screen shot methods.

 

[perco754]

I got it. Andrew you were on the right track asking about the rList, thanks!

My code was originally written with the A1 Referencs Style as a requirement. Sometime along the way some of the many users of this application has changed to R1C1 dito, that's why an address that evaluates to A1:A6 will produce a runtime error while the Workbook has the R1C1 style.
Now I just need to make sure using VBA that the book has the right style before allowing editing AND maybe prohibit users to change this. Anyone knows how to do that?

Thanks!

 

[Andrew Poulsom]

That's bitten me before now. You can set to A1 like this:

Application.ReferenceStyle = xlA1

Just put that at the beginning of your procedure.

 

[perco754]

Got it, thanks again!!

I also wanted to avoid users from doing this again in the future and found this code snip, works great!

Private Sub Workbook_Open()
    
    Dim Opt As Object
    Dim TB As Object
     
    Set TB = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
    Set Opt = TB.Controls("Options...")
    Opt.Enabled = False
    
    Application.ReferenceStyle = xlA1
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Opt As Object
    Dim TB As Object
     
    Set TB = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
    Set Opt = TB.Controls("Options...")
    Opt.Enabled = True
End Sub

BR,
perco

That's bitten me before now. You can set to A1 like this:

Application.ReferenceStyle = xlA1

Just put that at the beginning of your procedure.

 

[Andrew Poulsom]

That disables all the Options under Tools, which your users may not like. You don't need to prevent the user from changing the reference style if you reset it to A1 when necessary in your code.