vba 如何使用vba在excel中创建隐藏工作表的实例？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11200769/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create instances of hidden worksheet in excel using vba?
提问by Appy
I am creating an macro-enabled Excel as a tool for generating 'create table' sql script. The sheet is created where one needs to enter the column names, data type etc., and on a button click the script will be generated. This sheet is called 'Script Generator'. Now I need an 'Index' sheet which will have table names and a button. When I click the button I need to open 'script generator' sheets for each table name and these sheets should be renamed to the table name.
我正在创建一个启用宏的 Excel 作为生成“创建表”sql 脚本的工具。在需要输入列名、数据类型等的地方创建工作表，单击按钮将生成脚本。该工作表称为“脚本生成器”。现在我需要一个包含表名和按钮的“索引”表。当我单击按钮时，我需要为每个表名打开“脚本生成器”表，这些表应重命名为表名。
The index sheet code goes like this:
索引表代码如下：
Sub Add_sheets()
On Error Resume Next
Dim count As Integer
Dim r As Range
Dim sh As Worksheet
For Each r In Range("A3:A103")
If Not r = "" Then
count = 0
For Each sh In ActiveWorkbook.Sheets
If sh.Name = r.Value Then
count = count + 1
End If
Next sh
If count = 0 Then
With ActiveWorkbook.Sheets
.Add(after:=Worksheets(Worksheets.count), Type:="C:\Macro\Script_Template.xltm").Name = r.Value
End With
ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", _
SubAddress:=Sheets(r.Value).Name & "!A1"
End If
End If
Next r
End Sub
Now, the problem is I am adding the script generator saved as 'Script_Template.xltm' externally. I need only one Excel which will do this all. Means, the Index file should internally open/add the new sheets of the format 'script generator' so that it forms one complete tool. Maybe by hiding this sheet and calling its instances through macros and renaming those sheets. How to do it through VBA? Could someone help me with this?
现在，问题是我正在添加外部保存为“Script_Template.xltm”的脚本生成器。我只需要一个 Excel 就可以完成这一切。意味着，索引文件应该在内部打开/添加格式“脚本生成器”的新表，以便它形成一个完整的工具。也许通过隐藏此工作表并通过宏调用其实例并重命名这些工作表。如何通过VBA做到这一点？有人可以帮我解决这个问题吗？
回答by Mozan Sykol
Using True and False for setting the Visible property of a worksheet is not good practice. You should use the constants already provided - xlSheetHidden, xlSheetVeryHidden and xlSheetVisible.
使用 True 和 False 来设置工作表的 Visible 属性不是好的做法。您应该使用已经提供的常量 - xlSheetHidden、xlSheetVeryHidden 和 xlSheetVisible。
xlSheetVisible will make your sheet visible and xlSheetHidden will hide your sheet. Setting it to xlSheetVeryHidden will ensure that the only way you can make the sheet visible is through VBA and not through the Excel menubar Format -> Sheet -> Unhide.
xlSheetVisible 将使您的工作表可见，而 xlSheetHidden 将隐藏您的工作表。将其设置为 xlSheetVeryHidden 将确保使工作表可见的唯一方法是通过 VBA 而不是通过 Excel 菜单栏格式 -> 工作表 -> 取消隐藏。
Usage:
用法：
Sheets("Script_Template").Visible = xlSheetVisible
回答by Siddharth Rout
You can create a "Script_Template" sheet and hide it and then use this code create a copy
您可以创建一个“Script_Template”表并隐藏它，然后使用此代码创建一个副本
Sheets("Script_Template").Visible = True
Sheets("Script_Template").Copy After:=Sheets(ThisWorkbook.Sheets.count)
ActiveSheet.Name = r.Value
Sheets("Script_Template").Visible = False
