Cell notes following my cell selection

[Mrock1]

I have a quite complex workbook in which I used a Let function resolve what is to be displayed in a cell based on the same occurrence of an adjacent cell in that column. I mention this only because it was one of those cells that I was editing just before the problem started to happen. Unfortunately, this workbook is intellectual property and I cannot share it, but rest assured it contains many complex array formulas and some custom formulas. I really wish I could provide the nature of the trigger that is starting this problem, but, quite frankly it just seems to have reach some critical mass under which editing any cell will trigger the problem.
The problem is that when I select a cell that has a note associated with it, the note associated with another, seemingly random cell, immediately appears exactly on top of the selected cell. That same note will then move as I select other cells with their own associated notes (i.e. not if the selected cell doesn't have a note whereupon the note vanishes until I select a cell with a note. My Excel Options are set to display notes and comments only when hovering on a cell (i.e. tag only). This works normally whenever I hover the mouse over other such cells. But the random note always appears on top of the selected cell if that cell has a note of its own. When I say on top, I mean that is sizes automatically to the exact same size of the cell underneath, effectively obscuring the cell.

It's really very strange. I've had to revert to a version (it's stored in Teams) juts prior to the problem manifesting to eliminate the problem, but even when I edited a simple text cell elsewhere in the worksheet, it started again. My File-Options-Advanced-Display is set to "Indicators and notes, and comments on hover", yet this random not appears like I had chosen Edit note over the top of any cell I select that has its own note. In fact I can immediately click on the note and move it or edit it.

For what it's worth, the following is the formula I was working on at the time, which is repeated down a column in a table. It is effectively either displaying a previous value (using Hyperlink to jump to that previous value) if it IsDue. The IsDue function, apart from calculating if the cell falls due in the current month, based on a Start Date and Period on the same row, also will calculate as True the highest score in the previous months (to Jan) was < 0.2 or 20%. I only include this as this is the formula in the cell I was trying to augment to look ahead to Dec as well (FutureIneffective), but for some weird reason, that caused a circular reference on cells that didn't even relate to that formula - but that's another story, I guess.

=LET(Prev, MATCH([@[HBF Assurance Control '#]],$G$19:$G30,0),
ThisYr, DATE(YEAR(TODAY()),MONTH(AQ$19&"1"),1),
PrevRecordFound, ISNUMBER(Prev),
PastIneffective, MAX(IFERROR(VALUE(APRA_CPG_234Table33[@[Carry Over from Previous Years]:[Last Update Apr]]*MOD(COLUMN(APRA_CPG_234Table33[@[Carry Over from Previous Years]:[Last Update Apr]]),2)),0),[@[Carry Over from Previous Years]])<=0.2,
FutureIneffective, TRUE,
IsDue, OR(MOD(DATEDIF([@[Start Date]],ThisYr,"M")/VLOOKUP([@Period],MyPeriods,2,FALSE),1)=0, AND(PastIneffective, FutureIneffective)),
ValueAtPrev, INDEX([May],Prev-1),
HPLAofPrev, "#" & ADDRESS(Prev+18,COLUMN()),
HPLAofCurr, "#" & ADDRESS(ROW(),COLUMN()),
HeaderMonth, MONTH(AI$19&"1"),
IF(IsDue,
IF(PrevRecordFound,
IF(ValueAtPrev="Assessment Required",
HYPERLINK(HPLAofPrev,"Click here to record at row " & Prev+18),
HYPERLINK(HPLAofPrev,ValueAtPrev)
),
IF(IFERROR(DATEDIF(DATE(YEAR(TODAY()),HeaderMonth,1),TODAY(),"M"),-1)>=0,
HYPERLINK(HPLAofCurr,"Assessment Required"),
""
)
),
""
)
)

Among others, the area on the worksheet has a table with columns:
Carry Over from Previous Years, Jan, Last Update Jan, Feb, Last Update Feb,..., Dec, Last Update Dec

It is an outside chance that, while troubleshooting my formula and debugging my custom functions and other event macros, that I may have pressed some combination of F3, F5, F8, F9, followed by other keystrokes, while the worksheet was selected, not realising that the Visual Basic Environment was not the active window on a separate monitor. Maybe one of these keystrokes set up some odd setting in the worksheet that has precipitated the problem. I just don't know. I've tried pressing F5 (goto), F3 (paste name), F8 and F9 (not assigned) see if I'd changed a setting somehow, but I've not found anything useful,

I realise this is complex and that share the workbook would be ideal, but it does contain sensitive information and the workbook itself is intrinsically confidential, so I can't even remove the data.

I get the feeling that a copy and past of the worksheet to a new worksheet will not change anything much as I don't think it is caused by a corruption. After all, the note displays exactly the same size of the selected cell and on top of that cell, preventing viewing, other than on the formula bar. The fact that the note displays and changes size and allows direct editing suggests that I have managed to trigger Excel into some sort of buggy status, making it do something it isn't intended to do at all.

Thanks in advance for any ideas,

Max

 

[Mrock1]

I have an update. While it is coincidental that my problem started while I was working on the formula mentioned in my previous email, I have discovered empirically that if I execute a manual calculation (F9) a few times (number required varies) and then save the file, the problem stops. However, if I just do a file save on its own, the problem immediately manifests.

While the workbook is primarily stored in Teams on SharePoint to facilitate co-authoring, I've found that:
a) it's not just my PC and my user account that experiences the problem (both PCs are corporate PCs with the same installation of O365. I have yet to try my personal instance of O365 on a non-corporate PC);
b) the problem manifests even if I save the workbook to my local C:

I can admit that I have a couple of custom functions that are used widely in my worksheet formulas as listed below. I will add, however, that these custom functions have been in place for quite a long time, and working correctly. I also have some conditional formatting, some of which includes the Background function (below), but again, this has been working successfully for many months.

I have a named range:
Background = GET.CELL(63,INDIRECT("rc",FALSE))+N("Used as custom function to get current cell's background colour in a formula")
, a trick used to determine the background colour of the current cell.

The following custom functions are defined in VBA in Module1
'Get Range String
Function getRangeStr(startColNum As Long, endColNum As Long) As String
startCol = ColLtr(startColNum)
endCol = ColLtr(endColNum)

getRangeStr = startCol + ":" + endCol
End Function

'Convert column number to letter
Function ColLtr(iCol As Long) As String
If iCol > 0 And iCol <= Columns.Count Then ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")
End Function

'Split a string into an array
Function USplit(text As String, Optional delim As String)
If delim = "" Then dlm = "," Else dlm = delim
USplit = Split(text, dlm)
End Function

'Determine if value passed is a date
Function IS_DATE(rng) As Boolean
IS_DATE = IsDate(rng)
End Function

'Get the full name of the currently logged on AD user
Function GetCurrentUserFullName()
Dim WSHnet, UserName, UserDomain, objUser

Set WSHnet = CreateObject("WScript.Network")
UserName = WSHnet.UserName
UserDomain = WSHnet.UserDomain
Set objUser = GetObject("WinNT://" & UserDomain & "/" & UserName & ",user")
GetCurrentUserFullName = objUser.FullName
End Function

'Get the current Excel Sheet View name
Function GetSheetView()
Const TestName As String = "TestName"
Dim View As NamedSheetViewCollection
Set View = Sheet1.NamedSheetViews
Dim SheetView As NamedSheetView
Dim sh1 As Worksheet
Dim ActiveSheetView As String
Set sh1 = ThisWorkbook.Sheets(Sheet1.Name)
Set SheetView = sh1.NamedSheetViews.GetActive
On Error Resume Next
ActiveSheetView = SheetView.Name
If Err Or ActiveSheetView = "" Then ActiveSheetView = "Default"
On Error GoTo 0

GetSheetView = ActiveSheetView
End Function

'Set the current Excel Sheet View name
Function SetSheetView(ViewName As String)
Dim View As NamedSheetViewCollection
Set View = Sheet1.NamedSheetViews
Dim SheetView As NamedSheetView
Dim sh1 As Worksheet
Dim ActiveSheetView As String
On Error Resume Next

With Worksheets("APRA CPG 234 Tracking")
.Activate
.NamedSheetViews.GetItem(ViewName).Activate
End With

If Err Then 'The ViewName doesn't exist, so create and select it.
'Create the view
View.Add ViewName
'Select the view
With Worksheets("APRA CPG 234 Tracking")
.Activate
.NamedSheetViews.GetItem(ViewName).Activate
End With
End If
Set sh1 = ThisWorkbook.Sheets(Sheet1.Name)
Set SheetView = sh1.NamedSheetViews.GetActive
On Error GoTo 0
On Error Resume Next
'Get the currently active view
ActiveSheetView = SheetView.Name
'Return the active view name as the value of the function
SetSheetView = ActiveSheetView
On Error GoTo 0
End Function

'Returns first value to the left of the current month in range - see comments for details
Public Function LastRecordedScore(arr As Range, mnth)
'This function expects to be passed an 24x1 array representing the scores and assessment dates (or other comments) for each month Jan-Dec
'in the format:
'Jan score, Jan assessment date, Feb score, Feb assessment date, ... , Dec score, Dec Assessmnent date.
'As recored in the APRA CPG 234 Tracking Sheet monthly records APRA_CPG_234Table33[[Jan]:[Last Update Dec]}
Dim ResultArr() As Variant
ReDim ResultArr(arr.Columns.Count)
For I = 0 To 23
NewElement = (mnth * 2 + I) Mod 24
ResultArr(I) = arr(NewElement)
Next I
For I = 0 To 10 Step 2
OP1 = (11 - I) * 2 + I
Save1 = ResultArr(I)
Save2 = ResultArr(I + 1)
ResultArr(I) = ResultArr(OP1)
ResultArr(I + 1) = ResultArr(OP1 + 1)
ResultArr(OP1) = Save1
ResultArr(OP1 + 1) = Save2
Next I

I = 1
While ResultArr(I) = "" And I < 23
I = I + 2
Wend
If I = 23 Then
LastRecordedScore = CVErr(xlErrNA)
Else
LastRecordedScore = Val(ResultArr(I))
End If
End Function

'Returns second to last value in month range passed - refer to comments for details
Public Function PreviousToLastRecordedScore(arr As Range, mnth)
'This function expects to be passed an 24x1 array representing the scores and assessment dates (or other comments) for each month Jan-Dec
'in the format:
'Jan score, Jan assessment date, Feb score, Feb assessment date, ... , Dec score, Dec Assessmnent date.
'As recored in the APRA CPG 234 Tracking Sheet monthly records APRA_CPG_234Table33[[Jan]:[Last Update Dec]}
Dim ResultArr() As Variant
ReDim ResultArr(arr.Columns.Count)
For I = 0 To 23
NewElement = (mnth * 2 + I) Mod 24
ResultArr(I) = arr(NewElement)
Next I
For I = 0 To 10 Step 2
OP1 = (11 - I) * 2 + I
Save1 = ResultArr(I)
Save2 = ResultArr(I + 1)
ResultArr(I) = ResultArr(OP1)
ResultArr(I + 1) = ResultArr(OP1 + 1)
ResultArr(OP1) = Save1
ResultArr(OP1 + 1) = Save2
Next I

I = 1
FoundFirst = False
While (ResultArr(I) = "" Or Not FoundFirst) And I < 23
If ResultArr(I) <> "" Then FoundFirst = True
I = I + 2
Wend
If I = 23 Then
PreviousToLastRecordedScore = CVErr(xlErrNA)
Else
If FoundFirst Then
PreviousToLastRecordedScore = Val(ResultArr(I))
Else
PreviousToLastRecordedScore = CVErr(xlErrNA)
End If
End If
End Function