学习Excel技术，关注微信公众号：
excelperfect
在有些情况下，当工作簿发生变化时，有效性列表中的项目随之更新是很有用的。例如，如下图1所示，在“目录”工作表中，使用数据有效性列出了工作簿中所有工作表的名称，这可用于对工作表进行导航操作。对于含有大量工作表的工作簿来说，这尤其有用。
图1
当我们在工作簿中添加或者删除工作表时，想要该有效性列表能够自动更新，如下图2所示。
图2
可以使用VBA代码来实现。
按下Alt+F11组合键，打开VBE，插入一个标准模块，输入下面的代码：
Sub AddSheetsName()
Dim i As Integer
Dim strList As String
Dim wks As Worksheet
For Each wks In Worksheets
If wks.Name <> "目录" Then
strList = strList & wks.Name & ","
End If
Next wks
Worksheets("目录").Range("C2").ClearContents
With Worksheets("目录").Range("C2").Validation
.Delete
.AddType:=xlValidateList, Formula1:=strList
End With
Set wks =Nothing
End Sub
代码中，遍历工作簿中所有工作表，将除“目录”工作表之外的工作表名称使用“,”连接成字符串。然后清除单元格C2中的内容并删除其中存在的任何有效性列表，并添加新的有效性列表。
在VBE中，双击左侧工程资源管理器中的ThisWorkbook模块，在其代码窗口输入下面的代码：
Private Sub Workbook_NewSheet(ByVal Sh As Object)
AddSheetsName
End Sub
Private Sub Workbook_Open()
AddSheetsName
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh AsObject)
AddSheetsName
End Sub
关闭工作簿，然后再打开该工作簿，效果如上图2所示。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。
