I've been thinking about your explanation.
In an empty workbook , I've put some strings in range E:5 to E10. On sheet level, I run this code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyRange As Range Dim Loc As String Set MyRange = Range("E5:E10") If Not Intersect(Target, MyRange) Is Nothing Then MsgBox ("Change made to: " & Target.Address) Loc = Target.Value End If Call AcceptVar(Loc) End Sub
And on module level:
Click on a cell in E5 to E10, >>> A1 gets the value of the string clicked. Then, if you click anywhere outside that range, the string in A1 disappears.
It is exactly this behavior I don't understand. Why the 'reset' ? You would expect 'overwrite' as per VBA code.
Interestingly, if I place the call for the sub inside the If block, cell A1 remains as it is, that is, when clicking outside the E range. Click inside the E range and hurray, the desired change takes place. So yes, I am still scratching my head ....
Again, muchas gracias !