如何获得工作表代码名称以激活特定的工作表? [英] How can I get worksheet code name to activate a specific worksheet?

问题描述

我有一个工作表，其选项卡"名称为"Rpt_Group".我还将其代码名称重命名为shData.当我使用VBA通过"Rpt_Group"激活工作表时，它运行良好.但是，当我使用代码名称时，会收到一条错误消息

I have a worksheet with the "tab" name of "Rpt_Group". I also renamed its code name to shData. When I use VBA to activate the worksheet using "Rpt_Group" it runs fine. But when I use the code name I get an error message

"下标超出范围.

"subscript out of range.

这有效:WBA.Worksheets("Rpt_Group").Activate

这不起作用:WBA.Worksheets("shData").Activate

这不起作用:WBA.shData.Activate

Dim WBA As Workbook

'Open the desired workbook
Set WBA = Workbooks.Open(Filename:="path & file name")

'Activate the desired worksheet
WBA.Worksheets("Rpt_Group").Activate 'this works

这不起作用:WBA.Worksheets("shData").Activate

这不起作用:WBA.shData.Activate

推荐答案

这里是一种解决方案:

Sub tester()
    Dim WBA As Workbook

    Set WBA = Workbooks("Book1")
    WorksheetByCodeName(WBA, "codeNameHere").Activate

End Sub

'Get a worksheet with matching codeName (or Nothing if no match)
'    from a workbook wb
Function WorksheetByCodeName(wb As Workbook, codeName As String)
    Dim ws As Worksheet, rv As Worksheet
    For Each ws In wb.Worksheets
        If ws.codeName = codeName Then
            Set rv = ws
            Exit For
        End If
    Next ws
    Set WorksheetByCodeName = rv
End Function

可能想在尝试对返回值进行任何操作之前.

Probably want to check the return value before trying to do anything with it.

这篇关于如何获得工作表代码名称以激活特定的工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！