Excel选项卡表名称与Visual Basic工作表名称 [英] Excel tab sheet names vs. Visual Basic sheet names

问题描述

似乎Visual Basic不能根据用户修改的表单名称来引用工作表。工作表选项卡可以更改其名称，但似乎Visual Basic仍将工作表名称视为Sheet1等，尽管工作簿选项卡已更改为有用的功能。

我有这个：

  TABname = rng.Worksheet.Name'Excel表TAB名称，而不是VSB Sheetx名称。 
  

但是我想在Visual Basic例程中使用工作表名称。到目前为止我可以找到最好的是选择案例工作表选项卡与Visual Basic名称，这不会使我的一天。
Visual Basic必须知道Sheet1，Sheet2等名称。如何将这些与Excel选项卡名称相关联，以便我不必维护每个新的工作表或工作表选项卡重新命名更改的查找表？
提前感谢您的回复。

解决方案

在Excel对象模型中，工作表有两个不同的名称属性： / p>

Worksheet.Name

Worksheet.CodeName

Name属性是读/写并包含在工作表选项卡上显示的名称。它是用户和VBA可变的

CodeName属性是只读

您可以引用特定的表格
工作表（Fred）。范围（A1）其中Fred是.Name属性
或
Sheet1.Range（A1）其中Sheet1是工作表的代号。

It seems that Visual Basic can not reference sheets according to user-modified sheet names. The worksheet tabs can have their names changed, but it seems that Visual Basic still thinks of the worksheet names as Sheet1, etc., despite the workbook tab having been changed to something useful.

I have this:

TABname = rng.Worksheet.Name  ' Excel sheet TAB name, not VSB Sheetx name.

but I would like to use sheet names in Visual Basic routines. The best I could come up so far is to Select Case the Worksheet Tab vs. Visual Basic names, which doesn't make my day. Visual Basic must know the Sheet1, Sheet2, etc., names. How can I get these associated with the Excel tab names so that I don't have to maintain a look-up table which changes with each new sheet or sheet tab re-naming? Thanks in advance for your replies.

解决方案

In the Excel object model a Worksheet has 2 different name properties:

Worksheet.Name
Worksheet.CodeName

the Name property is read/write and contains the name that appears on the sheet tab. It is user and VBA changeable

the CodeName property is read-only

You can reference a particular sheet as Worksheets("Fred").Range("A1") where Fred is the .Name property or as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

这篇关于Excel选项卡表名称与Visual Basic工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！