VBA 如果不相交不适用于 worksheet_change 上的较大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18401881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA If Not Intersect not working for larger values on worksheet_change
提问by bmgh1985
I have the following code:
我有以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim btn As Button
Dim t As Range
Dim i As Integer
i = Target.Row
If Not Intersect(Target, Range("$B10:$B103")) Then
If Target.Value <> "" Then
For Each btn In ActiveSheet.Buttons
If btn.Name = "I" & i Then
btn.Delete
End If
Next btn
Set t = ActiveSheet.Range(Cells(i, 9), Cells(i, 9))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "imageshow"
.Caption = "View Images"
.Name = "I" & i
End With
Else
For Each btn In ActiveSheet.Buttons
If btn.Name = "I" & i Then
btn.Delete
End If
Next btn
End If
End If
End Sub
When I run it, it works if the value entered into B10:B103 is an Integer number, but if I use text or a Long number a combination of text and numbers (data entered in here will be of this form) then it will not work.
当我运行它时，如果输入到 B10:B103 的值是整数，它就可以工作，但是如果我使用文本或长数字，文本和数字的组合（此处输入的数据将采用这种形式），则它不会工作。
Does anyone have any ideas?
有没有人有任何想法？
Cheers
干杯
Ben
本
回答by Gary's Student
Change two lines:
改两行：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim btn As Button
Dim t As Range
Dim i As Long
i = Target.Row
If Not Intersect(Target, Range("$B10:$B103")) Is Nothing Then
If Target.Value <> "" Then
For Each btn In ActiveSheet.Buttons
If btn.Name = "I" & i Then
btn.Delete
End If
Next btn
Set t = ActiveSheet.Range(Cells(i, 9), Cells(i, 9))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "imageshow"
.Caption = "View Images"
.Name = "I" & i
End With
Else
For Each btn In ActiveSheet.Buttons
If btn.Name = "I" & i Then
btn.Delete
End If
Next btn
End If
End If
End Sub
