|
本帖最后由 lingyuncelia1 于 2016-4-11 16:50 编辑
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then
Set Rng = Sheets("数据库").Columns(1).Find(Target.Value, lookat:=xlWhole)
If Not Rng Is Nothing Then
Set c = [C2].MergeArea
Range("B2") = Rng.Offset(, 1)
Range("B3") = Rng.Offset(, 2)
Range("B4") = Rng.Offset(, 3)
For Each shp In ActiveSheet.Shapes '删除图片
If shp.Type = msoAutoShape Then shp.Delete
Next
Set PC = Range("C2").MergeArea
Rng.Offset(, 4).Copy PC '复制图片
For Each SP In Shapes '调整大小
With SP
.Height = PC.Height
.Width = PC.Width
End With
Next
End If
[f1].Select
End If
If Target.Address = "$b$2" Then
Set Rng2 = Sheets("数据库").Columns(2).Find(Target.Value, lookat:=xlWhole)
If Not Rng2 Is Nothing Then
Set c = [C2].MergeArea
Range("f1") = Rng2.Offset(, -1)
Range("B3") = Rng2.Offset(, 1)
Range("B4") = Rng2.Offset(, 2)
For Each shp In ActiveSheet.Shapes '删除图片
If shp.Type = msoAutoShape Then shp.Delete
Next
Set PC = Range("C2").MergeArea
Rng2.Offset(, 3).Copy PC '复制图片
For Each SP In Shapes '调整大小
With SP
.Height = PC.Height
.Width = PC.Width
End With
Next
End If
End If
End Sub
lingyuncelia1 发表于 2016-4-11 14:34
改为大写后,改变F1的值,出现上图,按“调试”键,出现下图:
循环调用的原因,最终导致资源耗尽.
在if语句下面加上语句关闭事件响应,endif前再重新开启事件响应. 类似下面这样.
If Not Rng2 Is Nothing Then
Application.EnableEvents = False
'...
Application.EnableEvents = True
End If
|
|