如何根据在另一个相应单元格中输入的某些值范围限制单元格中的数据输入 [英] How to restrict data entry in cells based on some range of values entered in another corresponding cell
问题描述
HI,
我在A列中有大量的3位数代码。只有16个代码可以访问B列中相应单元格中的数据输入以及所有其他单元格对应于16个代码以外的代码不应接受列
B中相应单元格中的任何数据。
有什么解决方案
立刻的帮助将不胜感激。
创建16个允许的命名范围名为" Allowed"的代码 (不带引号)允许VBA代码检查A列中的有效代码
  1)复制此代码。
  2)右键单击感兴趣的工作表选项卡。
  3)选择"查看代码"
  4)将代码粘贴到出现的窗口中。
  5)将文件另存为启用宏的.xlsm文件。
  6)根据需要进行更改
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
 如果相交(目标,范围("B:B"))则为Nothing然后退出Sub
  '关闭事件以避免循环
  Application.EnableEvents = False
 对于每个c In Target
$
      Dim v As Variant
      v = Application.Match(c.Offset(0,-1).Value,Range(" Allowed"),False)
     如果是IsError(v)则为
          Application.Undo
          MsgBox"甚至没想到这样做!"b
     结束如果
 下一页c
$ b   '重新开启活动,为下次更改做好准备。
  Application.EnableEvents = True
End Sub
                                  
HI ,
I have huge amount of 3 digit codes in Column A. Only 16 codes should have access to data entry in respective cell in column B and all other cells corresponding to codes other than 16 codes should not accept any data in the respective cells in Column B.
What are the solutions
Immediate help would be appreciated.
Create a named range of your 16 allowed codes named "Allowed" (without the quotes) to allow the VBA code to check for valid codes in column A
1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
'Turn off events to keep out of loops
Application.EnableEvents = False
For Each c In Target
Dim v As Variant
v = Application.Match(c.Offset(0, -1).Value, Range("Allowed"), False)
If IsError(v) Then
Application.Undo
MsgBox "Don't even think of doing that!"
End If
Next c
'Turn events back on to get ready for the next change
Application.EnableEvents = True
End Sub
这篇关于如何根据在另一个相应单元格中输入的某些值范围限制单元格中的数据输入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!