VBA实例-点击式选择

在VBA讨论群中出现如图一个问题

  • 1.在vbe代码窗口中,点击资源管理器sheet1,输入以下代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 10 And Target.Row <= 20 And Target.Count = 1 Then
If Application.WorksheetFunction.CountA(Range("22:22")) = 0 Then
Cells(22, 2) = Target.Value
ElseIf Application.WorksheetFunction.CountA(Range("22:22")) = 1 Then
Cells(22, 3) = Target.Value
ElseIf Application.WorksheetFunction.CountA(Range("22:22")) > 10 Then
MsgBox "已选中10人"
Exit Sub
Else
Cells(22, 2).End(xlToRight).Offset(0, 1) = Target.Value
End If
End If
End Sub

查看实例 点击下载
效果如下
00.gif

  • 2.保存,表格存为启用宏的工作簿。
  • 3.Excel2016测试有效。