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?

CodePudding user response：

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.

CodePudding user response：

Found solution. Since even if the sheet is protected with the parameter UserInterfaceOnly:=True and AllowInsertingRows:=True the excel table itself will not be able to automatically add a new row below the last one, for me I had three working solutions:

1. Use the intersect method with EntireColumn instead DataBodyRange range.
2. Use a table to which a row has already been added with the ListObject.ListRows.Add method, each time you fill a value to last row, a new empty row is added. In this case, you can continue to use the intersect method with the DataBodyRange range.
3. Since the worksheet does not have a before change event, the Worksheet.SelectionChange event can be used in the work area, so while waiting for the entered data, the protection is already removed in time, allowing the excel table to automatically add a row. Changing the selection to any other cell will protect the page.