首页 文章

Excel动态工作表名称

提问于
浏览 696
1

你知道是否有办法找到所有表格的名称作为列表?我可以通过以下公式找到放置公式的工作表的工作表名称:

=RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-SEARCH("]";CELL("filename";A1);1))

但这只适用于放置公式的工作表 . 有人知道如何在一张纸上获得文件中所有工作表的列表(假设我在单元格A1:A5,如果我有5张纸)?我想制作一些宏的动态,所以当有人更改工作表名时,宏会继续工作 . 对不起我的英语不好..

5 回答

  • 3

    一个VBA函数,如:

    Function SheetName(ByVal Index As Long, Optional ByVal Book as Range) as String
        Application.Volatile
        If Book Is Nothing Then Set Book = Application.Caller
        SheetName=Book.Worksheet.Parent.Sheets(Index).Name
    End Function
    

    将按索引返回工作表名称,如Excel公式 . 例:

    =SheetName(1) 'returns "Sheet1"
    =SheetName(3) 'returns "Sheet3"
    

    在另一本书中使用可选范围,您可以获得其他书籍表名称:

    =SheetName(1, [Some other book.xls]Sheet1!A1) 'returns "Sheet1"
    =SheetName(2, [Some other book.xls]Sheet1!A1) 'returns "Sheet2"
    
  • 1

    @Mischa使用以下代码的Urlings作为消息在消息框中显示以下内容:

    • 工作表名称

    • 工作表位置

    Option Explicit
    
    Sub test()
    
    Dim ws As Worksheet
    Dim str As String
    
    For Each ws In ThisWorkbook.Worksheets
        str = str & vbNewLine & "Sheet named " & ws.Name & " located in position " & ws.Index & "."
    Next
    
    'Get the names in a list in message box
    MsgBox str
    
    End Sub
    
  • 0

    创建一个已定义的名称(公式,名称管理器):名称:字段中的YourSheetNames指的是您所在的位置:

    =IF(NOW()>0,REPLACE(GET.WORKBOOK(1),1;FIND("]",GET.WORKBOOK(1)),""))
    

    在你的工作表中,你放在A1:A5:

    =INDEX(YourSheetNames,ROW())
    

    这将给你(只要计算设置为xlautomatic)一个实际的列表

  • 0

    顺便说一句,在vba中,您可以按名称或按对象引用工作表 . 如果您使用第一种引用工作表的方法,它将始终使用任何名称,如下所示 .

  • 0

    我会保留一张非常隐藏的表格,其中包含您参考每张表格所使用的公式 .

    Workbook_NewSheet 事件触发时,会创建指向新工作表的公式:

    • 创建工作表并为其指定代码 shtNames .

    • 为工作表提供选项卡名称 SheetNames .

    • shtNames 的单元格 A1 中添加 Headers (我刚使用"Sheet List") .

    • 在工作表更改的属性中可见2 - xlSheetVeryHidden .
      只有至少留下一张可见的纸张时才能执行此操作 .

    • 将此代码添加到 ThisWorkbook 模块:


    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    
        With shtNames
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Formula = _
                "=RIGHT(CELL(""filename"",'" & Sh.Name & "'!$A$1), " & _
                "LEN(CELL(""filename"",'" & Sh.Name & "'!$A$1))-" & _
                "FIND(""]"",CELL(""filename"",'" & Sh.Name & "'!$A$1),1))"
        End With
    
    End Sub
    

    在名称管理器中创建命名范围:

    • 我叫它 SheetList .

    • 使用此公式:
      =SheetNames!$A$2:INDEX(SheetNames!$A:$A,COUNTA(SheetNames!$A:$A))

    然后,您可以使用 SheetList 作为数据验证列表和列表控件的源 .

    我还没有看到的两个潜在问题是重新排列纸张并删除纸张 .

    所以当有人更改工作表名称时,宏会继续工作

    正如@SNicolaou所说 - 使用用户无法更改的工作表代码名称,无论工作表标签名称如何,您的代码都将继续工作 .

相关问题