通过VBA代码重命名工作表代码名称(不通过属性重命名) [英] Renaming a sheet code name through VBA code (Not through properties)

问题描述

我为用户提供了一个受保护的工作簿A，该用户不允许用户从另一个工作簿B复制工作表.在工作簿AI中，请考虑工作表1-19(到目前为止只有13个工作表，以后我允许19个工作表扩展为工作簿)作为系统表"，并且无法删除或修改.工作表20-30是非系统工作表"，用户可以在其中根据需要删除和修改.

我正在寻找一种导入功能，用户可以将包含1个工作表的工作簿B导入工作簿A.工作簿A会检查以查看当前存在多少个非系统工作表"，并将新工作表插入到工作表A中.结尾为20+的工作表编号.例如:

Total sheets in workbook A = 18

Total "system sheets" in A = 13 (sheet 1-sheet 13)

Total "non system sheets" in A = 5 (sheet 20-sheet 24)

如果我要导入新的工作表，则A会将其分配为工作表25

我希望VBA更改代码名称(工作表编号)，而不要更改工作表的名称.

我已经尝试搜索方法，有人可以为我指出正确的方法吗?谢谢！

解决方案

在Excel对象模型中，工作表具有2个不同的名称属性:

Worksheet.Name Worksheet.CodeName

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

CodeName属性为只读

您可以将特定工作表称为Worksheets("Data").Range("A1")，其中Data是.Name属性，也可以作为Sheet1.Range("A1")引用，其中Sheet1是工作表的代号. /p>

您可以通过访问VBA项目模型组件的可扩展性来更改CodeName属性.

ThisWorkbook.VBProject.VBComponents(Sheets("Sheetname").CodeName).Name = "Sheet" & Workbook.Sheets.Count

只需确保可以以编程方式访问Visual Basic项目.

文件->选项->信任中心->信任中心设置->宏设置->信任对VBA项目对象模型的访问.

I have a protected workbook A for the user which does not allow the user to copy over a sheet from another workbook B. In workbook A I consider worksheets 1-19 (only have 13 sheets as of now, I allowed 19 for future expansion of the workbook) as "system sheets" and cannot be deleted or modified. Sheets 20-30 are "non system sheets" where the user can delete and modify as needed.

I'm looking to have an import function where the user can import workbook B that contains 1 sheet into workbook A. workbook A would check to see how many "non system sheets" that currently exists and insert the new sheet at the end with a sheet number that is 20+. For example:

Total sheets in workbook A = 18

Total "system sheets" in A = 13 (sheet 1-sheet 13)

Total "non system sheets" in A = 5 (sheet 20-sheet 24)

If I were to import a new sheet workbook A would assign it as sheet 25

I want VBA to change the code name(sheet number) and not the name of the sheet.

I've tried searching for hows, can someone please point me in the right direction on how to do this? thanks!

解决方案

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("Data").Range("A1") where Data is the .Name property or as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

edit: You can change the CodeName Property by accessing the VBA Project Model Components Extensibility.

ThisWorkbook.VBProject.VBComponents(Sheets("Sheetname").CodeName).Name = "Sheet" & Workbook.Sheets.Count

Just be sure to have the programmatic access to visual basic project.

File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object model.

这篇关于通过VBA代码重命名工作表代码名称(不通过属性重命名)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！