首页 > Excel专区 > Excel教程 >

excel如何实现下拉框复选

Excel教程 2021-08-19 14:15:53

excel如何实现下拉框复选?

EXCEL选择下拉框实现复选

第一步:新建一个excel且设置数据有效性【选中X列--数据--有效性】

f9a678bb5fc411d5c29cd3468d7c3cd.png

第二步:开发工具--查看代码--把代码复制进去保存就OK了

972183b6c4b676ed0be6aed019613cb.png

代码如下:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

Private Sub Worksheet_Change(ByVal Target As Range)

' Developed by Contextures Inc.

' www.contextures.com

Dim rngDV As Range

Dim oldVal As String

Dim newVal As String

If Target.Count > 1 Then GoTo exitHandler

  

On Error Resume Next

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

On Error GoTo exitHandler

  

If rngDV Is Nothing Then GoTo exitHandler

  

If Intersect(Target, rngDV) Is Nothing Then

   'do nothing

Else

  Application.EnableEvents = False

  newVal = Target.Value

  Application.Undo

  oldVal = Target.Value

  Target.Value = newVal

  If Target.Column = 7 Then '这里规定好哪一列的数据有效性是多选的,A列是第1列,依次类推,如3就是C列,7就是G列

    If oldVal = "" Then

      'do nothing

      Else

      If newVal = "" Then

      'do nothing

      Else

        If InStr(1, oldVal, newVal) <> 0 Then  '重复选择视同删除

          If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复

            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)

          Else

            Target.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号

          End If

        Else '不是重复选项就视同增加选项

        Target.Value = oldVal & "," & newVal

'      NOTE: you can use a line break,

'      instead of a comma

'      Target.Value = oldVal _

'        & Chr(10) & newVal

        End If

      End If

    End If

  End If

End If

  

exitHandler:

  Application.EnableEvents = True

End Sub


标签: excel下拉框复选

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.