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?
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.