CODEWITHSUNDEEP
excelvba
czchlong
czchlong

Reputation: 2584

Excel/Excel VBA cut and copy resets cell name

The situation is simple, if I name cell A1 to be MY_CELL and then CUT AND PASTE from cell A2 to cell MY_CELL, then MY_CELL will lose that name and be named A1 again.

However, this does not happen when I COPY AND PASTE from A2 to MY_CELL.

How can I prevent Excel from resetting cell names on CUT AND PASTE?

Upvotes: 3

Views: 1099

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Logic: Trap the changes to Cell A1 using the Worksheet_Change event and recreate the name if it is lost.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DoesRngNameExists As Boolean
    Dim sName As String

    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> You named range
    sName = "MY_CELL"

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        '~~> Check if the Range exists
        On Error Resume Next
        DoesRngNameExists = Len(Names(sName).Name) <> 0
        On Error GoTo 0

        '~~> If not then recreate it
        If DoesRngNameExists = False Then _
        ThisWorkbook.Names.Add Name:=sName, RefersToR1C1:="=Sheet1!R1C1"
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Note: The codes in the Sheet code area of the relevant sheet. See screenshot below.

enter image description here

Upvotes: 2

Related Questions