我有一个工作表调用用户，在Q列第10-19行，我希望用户能够从下拉列表中选择是或否，但该列中只能设置一个条目。例如：如果他们在Q11中选择是，但在Q13中选择了某个选项，则应设置Q13，取消设置Q11。我已经创建了一个名为YesNo的命名列表，并使用列表数据验证将其应用于Q10：19，但如何将列限制为只允许一个条目？？谢谢
f1tvaqid1#
您可以使用事件代码：要进入此事件触发宏，请右键单击工作表选项卡。从右键单击下拉菜单中选择“查看代码”。然后将下面的代码粘贴到打开的窗口中。
Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range) Dim rg As Range Dim c As Range Set rg = Range("Q10:Q19")'is the changed cell in the Range to checkIf Not Intersect(Target, rg) Is Nothing Then'Did you enter something in that cell, or just clear it? If Len(Target) > 0 Then 'Don't trigger the event endlessly Application.EnableEvents = False For Each c In rg 'make sure to not clear the cell we just changed If Intersect(c, Target) Is Nothing Then c.ClearContents Next c End IfEnd If're-enable the event methodApplication.EnableEvents = True End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Dim c As Range
Set rg = Range("Q10:Q19")
'is the changed cell in the Range to check
If Not Intersect(Target, rg) Is Nothing Then
'Did you enter something in that cell, or just clear it?
If Len(Target) > 0 Then
'Don't trigger the event endlessly
Application.EnableEvents = False
For Each c In rg
'make sure to not clear the cell we just changed
If Intersect(c, Target) Is Nothing Then c.ClearContents
Next c
End If
're-enable the event method
Application.EnableEvents = True
End Sub
字符串
1条答案
按热度按时间f1tvaqid1#
您可以使用事件代码：
要进入此事件触发宏，请右键单击工作表选项卡。从右键单击下拉菜单中选择“查看代码”。然后将下面的代码粘贴到打开的窗口中。
字符串