5,139
社区成员
发帖
与我相关
我的任务
分享
'需求:修改下面的代码,根据 C7、G12单元格的不同选择,从同路径下面两个子文件夹里面,提取同名图片,放置在 25行以下的固定位置,大小跟随存放的单元格大小相同。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Union([C7], [G12])
If Intersect(rng, Target) Is Nothing Then Exit Sub
Dim path$, rg As Range, shp As Shape, ad$
Application.ScreenUpdating = False
Set rg = Target.Offset(18, 0) '【问题1】 C7 和 G12 偏移到25行的差值是两个数字,这里需要修改代码
ad = rg.Address
path = ThisWorkbook.path & "\产品图片\" '【问题2】 图片放置在2个路径的文件夹里面,怎么样修改这里的代码
path = ThisWorkbook.path & "\开启方向\"
For Each shp In ActiveSheet.Shapes
' If shp.TopLeftCell.Address = ad Then shp.Delete 【问题3】 当C7 和 G12 选择不同的数值时,怎么执行该语句 清楚原来的图片
Next
If Len(Target.Value) > 0 Then
ActiveSheet.Pictures.Insert(path & Target.Value & ".jpg").Select
With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Top = rg.Top
.Left = rg.Left
.Height = rg.MergeArea.Height
.Width = rg.MergeArea.Width
End With
End If
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If [G13] = "不需要" And [G8] = "不需要" Then
With ActiveSheet
Application.EnableEvents = False
.Unprotect
.UsedRange.Cells.Locked = False
.Range("H13") = "Choose an item."
.Range("H13,b5").Locked = True
.Protect
.EnableSelection = xlUnlockedCells
Application.EnableEvents = True
End With
Else
ActiveSheet.Unprotect
End If
End Sub