根据单元格值的EXCEL VBA动态工作表名称-单元格中的公式时不起作用 [英] EXCEL VBA Dynamic Sheet Name according to a cell value - Not working when formula in the cell

问题描述

嘿，

我创建了一个小的VBA代码来动态重命名工作表.

I've created a small VBA code to dynamically rename a worksheet.

仅在手动键入单元格时，它可以正常工作.

It's working perfectly when the cell is just manually typed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C9")) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("C9")

    End If
End Sub

但是，一旦我将公式连接到C9单元格中的2个单元格值，它就不会自动更新它.为了使其正常工作，我需要输入单元格，然后再次输入ENTER，它才能正常工作.每次更改串联的2个单元格中的on的值时，我都必须执行相同的操作.

But then as soon as I will put a formula concatenating 2 cells values within C9 cell it will not update it automatically. To make it work I need to enter the cell and type ENTER again and it works. I have to do same manipulation each time I change a value in on of the 2 cell concatenated.

感谢您的帮助人员

推荐答案

您需要捕获不同事件:

  Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
        ActiveSheet.Name = ActiveSheet.Range("C9")
    Application.EnableEvents = True
 End Sub

注意:

如果工作表包含引用选项卡名称的公式，我们将在名称更改期间禁用事件.

We disable events during the name change in case the worksheet contains a formula referencing the tab-name.

这篇关于根据单元格值的EXCEL VBA动态工作表名称-单元格中的公式时不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！