Question

excel 如何用vba 实现 某列 限制输入范围

Answer 1

excel
如何用vba
实现
某列
限制输入范围的解决方法如下：
1、打开要处理的excel文档，
2、按alt+f11,打开vba编辑窗口，
3、双击左侧树中的thissheet,
4、在右侧的空白窗口中输入如下的内容：
Private
Sub
Worksheet_Change(ByVal
Target
As
Range)
Dim
s
As
String
Dim
i
As
Long,
j
As
Long
Dim
LL
As
String
LL
=
"D"
'需要控制的列
Dim
cc
As
Range
For
Each
cc
In
Target
If
InStr(1,
cc.Address,
LL)
>
0
Then
s
=
cc.Value2
'MsgBox
s
If
s
=
"文本"
Or
s
=
"数字"
Or
s
=
"日期"
Then
Else
cc.ClearContents
End
If
End
If
Next
End
Sub

Answer 2

option explicit
private sub worksheet_change(byval target as range)
dim i as long
i = target.row
if i > 3 and target.column = 11 then
columns("d:d").numberformatlocal = "yyyy/m/d h:mm;@"
if cells(i, 3) = cells(i - 1, 3) then
cells(i, 4) = cells(i - 1, 4)
else
cells(i, 4) = now()
end if
end if
end sub这个是工作表事件过程，一定要放在工作表对应的代码中窗口中。