Сохранить макрос листа, чтобы связать ячейки с изменением имени листа

Question

У меня есть две ячейки с именами INPUT_A_1 и INPUT_A_2 на листах с именами "Лист1" и "Лист2" ​​соответственно, которые я связываю (изменение одной ячейки вызывает одинаковое изменение в другой) со следующими макросами листа, которые работают очень хорошо:

В листе 1:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_1").Address) Then
  Sheets("Sheet2").Range("INPUT_A_2") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

и в Листе 2:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_2").Address) Then
  Sheets("Sheet1").Range("INPUT_A_1") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

Моя проблема заключается в том, что из-за синтаксиса Sheets (sheetname).Range (rangename), если я решу переименовать одну или обе таблицы, я должен соответствующим образом изменить макросы. Есть ли какой-то обходной путь к этому, который не включает в себя вызов ячеек по имени соответствующего листа? Эта проблема становится все более очевидной, когда у меня есть 3 или более связанных ячеек в каждой отдельной таблице.

Спасибо

Answer 1

Фактически, вместо этого можно использовать индекс листа, тем самым устраняя необходимость в процедуре SheetFromCodeName.

Синтаксис в этом случае на первом и втором листах будет

Worksheets(2).Range("INPUT_A_2") = Target.Value

а также

Worksheets(1).Range("INPUT_A_1") = Target.Value

Answer 2

"Обходной путь" - вместо этого использовать кодовое имя рабочего листа.

Использование кодового имени рабочего листа

Лучший способ доступа к рабочему листу - использовать кодовое имя. Каждый лист имеет имя листа и кодовое имя. Имя листа - это имя, которое отображается на вкладке листа в Excel.

Изменение имени листа не меняет кодовое имя, а это означает, что ссылка на лист по кодовому имени является хорошей идеей.

Answer 3

Следуя вышеприведенному предложению Storax, вот исправление, которое я реализовал:

На первом листе (который можно переименовать по желанию):

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_1").Address) Then
  SheetFromCodeName("Sheet2").Range("INPUT_A_2") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

и во втором листе (который также может быть переименован по желанию):

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_2").Address) Then
  SheetFromCodeName("Sheet1").Range("INPUT_A_1") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

И, наконец, в любом модуле:

Public Function SheetFromCodeName(CodeName$) As Worksheet
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.CodeName = CodeName Then
           Set SheetFromCodeName = sh
           Exit For
        End If
    Next sh
End Function

Ассоциация SheetName и CodeName соответствует:

Если у вас есть несколько листов со связанными ячейками, и любой из листов будет удален, должно работать "При возобновлении ошибки далее".