VBA小技巧11：创建动态有效性列表

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社群，进行技术交流和提问，获取更多电子资料。