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

fanjy

发布于 2021-07-30 10:00:28

1.3K0

发布于 2021-07-30 10:00:28

文章被收录于专栏：完美Excel

在有些情况下，当工作簿发生变化时，有效性列表中的项目随之更新是很有用的。例如，如下图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所示。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-07-21，如有侵权请联系 cloudcommunity@tencent.com 删除

vba