Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 3580|回复: 7

[已解决]一个一级菜单和多个二级菜单(做下拉选项),VBA代码修改

[复制链接]
发表于 2017-7-17 12:19 | 显示全部楼层 |阅读模式
一级菜单和二级菜单1的下拉选项的代码老师已经帮忙写好了,现在需要在让代码增加两项,让二级菜单2和二级菜单3都具有下拉选项,该选项均与一级菜单对应,这是一个一级菜单和多个二级菜单,并非四级菜单,拜谢!
详见附件
最佳答案
2017-7-17 15:59
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.      If Target.Count > 1 Then Exit Sub
  3.      If Intersect(Target, [b2:b10]) Is Nothing Then Exit Sub
  4.      Target.Offset(, 1).Resize(, 3).Validation.Delete
  5.      Target.Offset(, 1).Resize(, 3).ClearContents
  6. End Sub

  7. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  8.    Dim TheList As String, i&
  9.    If Target.Count > 1 Then Exit Sub
  10.    If Intersect(Target, [b2:e10]) Is Nothing Then Exit Sub
  11.    Set d = CreateObject("scripting.dictionary")
  12.    arr = Sheet2.Range("c2:f" & Sheet2.[c65536].End(3).Row)
  13.    For i = 1 To UBound(arr)
  14.       x = arr(i, 1)
  15.       If Len(x) Then
  16.         If InStr(d(x) & ",", "," & arr(i, 2) & ",") = 0 Then d(x) = d(x) & "," & arr(i, Target.Column - 1)
  17.       End If
  18.    Next
  19.    
  20.    TheList = IIf(Target.Column = 2, Join(d.keys, ","), Mid(d(Cells(Target.Row, 2).Value), 2))
  21.    With Target.Validation
  22.       .Delete
  23.       .Add xlValidateList, , , TheList
  24.    End With
  25. End Sub
复制代码

Book3.rar

12.15 KB, 下载次数: 68

发表于 2017-7-17 13:07 | 显示全部楼层
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.      If Target.Count > 1 Then Exit Sub
  3.      If Intersect(Target, [b2:b10]) Is Nothing Then Exit Sub
  4.      Target.Offset(, 1).Resize(, 3).Validation.Delete
  5.      Target.Offset(, 1).Resize(, 3).ClearContents
  6. End Sub

  7. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  8.    Dim TheList As String, i&
  9.    If Target.Count > 1 Then Exit Sub
  10.    If Intersect(Target, [b2:e10]) Is Nothing Then Exit Sub
  11.    Set d = CreateObject("scripting.dictionary")
  12.    arr = Sheet2.Range("c2:d" & Sheet2.[c65536].End(3).Row)
  13.    For i = 1 To UBound(arr)
  14.       x = arr(i, 1)
  15.       If Len(x) Then
  16.         If InStr(d(x) & ",", "," & arr(i, 2) & ",") = 0 Then d(x) = d(x) & "," & arr(i, 2)
  17.       End If
  18.    Next
  19.    
  20.    TheList = IIf(Target.Column = 2, Join(d.keys, ","), Mid(d(Cells(Target.Row, 2).Value), 2))
  21.    With Target.Validation
  22.       .Delete
  23.       .Add xlValidateList, , , TheList
  24.    End With
  25. End Sub
复制代码
回复

使用道具 举报

 楼主| 发表于 2017-7-17 13:16 | 显示全部楼层

非常感谢,您的代码在二级菜单2和二级菜单3的下拉选项均为二级菜单1的内容啊,我想二级菜单2下拉内容就是二级菜单2的,二级菜单3下拉内容就是二级菜单3的,能否再改一改,感谢!
回复

使用道具 举报

发表于 2017-7-17 15:59 | 显示全部楼层    本楼为最佳答案   
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.      If Target.Count > 1 Then Exit Sub
  3.      If Intersect(Target, [b2:b10]) Is Nothing Then Exit Sub
  4.      Target.Offset(, 1).Resize(, 3).Validation.Delete
  5.      Target.Offset(, 1).Resize(, 3).ClearContents
  6. End Sub

  7. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  8.    Dim TheList As String, i&
  9.    If Target.Count > 1 Then Exit Sub
  10.    If Intersect(Target, [b2:e10]) Is Nothing Then Exit Sub
  11.    Set d = CreateObject("scripting.dictionary")
  12.    arr = Sheet2.Range("c2:f" & Sheet2.[c65536].End(3).Row)
  13.    For i = 1 To UBound(arr)
  14.       x = arr(i, 1)
  15.       If Len(x) Then
  16.         If InStr(d(x) & ",", "," & arr(i, 2) & ",") = 0 Then d(x) = d(x) & "," & arr(i, Target.Column - 1)
  17.       End If
  18.    Next
  19.    
  20.    TheList = IIf(Target.Column = 2, Join(d.keys, ","), Mid(d(Cells(Target.Row, 2).Value), 2))
  21.    With Target.Validation
  22.       .Delete
  23.       .Add xlValidateList, , , TheList
  24.    End With
  25. End Sub
复制代码

评分

参与人数 1 +1 收起 理由
cpl275538 + 1 我和小伙伴都惊呆了,太感谢了

查看全部评分

回复

使用道具 举报

发表于 2017-7-17 16:00 | 显示全部楼层
其实就改了这一句,自己琢磨吧。
If InStr(d(x) & ",", "," & arr(i, 2) & ",") = 0 Then d(x) = d(x) & "," & arr(i, Target.Column - 1)
回复

使用道具 举报

 楼主| 发表于 2017-7-17 17:11 | 显示全部楼层

感谢帮助,只是二级菜单2和二级菜单3,下拉选项还是有重复内容啊,   如“北京”对应二级菜单2是“a,b,a”  下拉选项应为“a,b”
回复

使用道具 举报

发表于 2017-7-18 08:41 | 显示全部楼层
漏了一处,你把这句替换下。
If InStr(d(x) & ",", "," & arr(i, Target.Column - 1) & ",") = 0 Then d(x) = d(x) & "," & arr(i, Target.Column - 1)
回复

使用道具 举报

 楼主| 发表于 2017-7-18 09:48 | 显示全部楼层
大灰狼1976 发表于 2017-7-18 08:41
漏了一处,你把这句替换下。
If InStr(d(x) & ",", "," & arr(i, Target.Column - 1) & ",") = 0 Then d(x ...

真是神人啊  太感谢了
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 09:39 , Processed in 0.277009 second(s), 14 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表