excelvbaruntime-errorset-intersection

Interesting problem with VBA error 91, protection and intersetcion


Please, could anyone help with error 91, I can't find a solution. Firstly the related code part:

.......
Application.EnableEvents = False 
PrUpr.UJournal 'Protection code for sheet is located in  PrUpr module.
Intersect(targetRow, rgRecName) = foundName '!! Here the problem comes out !!
.......
PrUpr.PJournal
.......

Where PrUpr.UJournal contains many subs including:

Sub UJournal()
 ThisWorkbook.Worksheets("Journal").Unprotect "123"
End Sub

The protection was activated with the code:

Sub PJournal()
 With ThisWorkbook.Worksheets("Journal")
   .Protect Password:="123", UserInterfaceOnly:=True, AllowFormattingCells:=True, 
    AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=False, 
    AllowFiltering:=True, AllowDeletingRows:=True
   .EnableSelection = xlNoRestrictions
 End With
End Sub

targetRow (entire row range), rgRecName(column range) And foundName(string) All contain readable values, however Intersect(targetRow, rgRecName) value is nothing.

The code works under the event Worksheet_Change(ByVal Target As Range), everything works fine until I turn on the protection of the sheet with a macro and then try to unprotect it. No ActiveSheet and commands like this is used in the code. The code always works when I use debug mode step by (F8) from the beginning, but when it works automatically it throws an error. Wait doesn't help either. If the code starts working with an unprotected sheet, then everything works fine until protection is enabled by code (or manually). UserInterfaceOnly:=True / False unfortunately has no effect. Workbook_Open doesn't contains protection-related objects Setting the whole sheet cells parameter to locked or unlocked does nothing. Two userforms are open while the code is running. Maybe someone has faced such a problem?


Solution

  • There's quite a bit to unpack here:

    Intersect(targetRow, rgRecName) = foundName
    

    Intersect will return a Range object representing the cells that intersect between the two provided ranges; if no cell intersects, that's when you get Nothing.

    Now that's on the LHS of a value assignment, so what VBA is trying to do here is to let-coerce the RHS value into the LHS object, and it can only do this by invoking that object's default member - but if there is no object reference, then the member call against Nothing does what it does every time: it raises run-time error 91.

    So you're getting error 91 because rgRecName isn't intersecting with TargetRow.

    Typically you would use the result of Intersect together with Is Nothing as part of a conditional where you run some code when there's an intersection - since the intersection of a row with a column is necessarily a single cell the assignment is safe, but still declare and set a variable to avoid doing the work twice:

    Dim intersection As Range
    Set intersection = Intersect(targetRow, rgRecName)
    If Not intersection Is Nothing Then
        intersection.Value = foundName
    Else
        Debug.Print rgRecName.Address & " does not intersect " & targetRow.Address
    End If
    

    Look into why the intersection is Nothing if that's unexpected; it's not clear from your post how it's being assigned.