melsahn
06-01-2009, 11:43 AM
Whats up guys? Just needed some help here with some code I would appreciate any help of course I just cant seem to figure this out...
What I am trying to do here is select a value from a drop down which is linked to a formula which triggers the macro shape..i have all that down already... but the problem is that I will have multiple values in the same drop down and I needed to figure a way to delete the previous macro shape in that range. And so i have created the delete all shapes code below but it seems whenever I use the code it deletes the data validation drop downs
here is what I have so far...
Function Macro()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _
Select
End Function
Function CIRCLES()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeOval, 203.25, 101.25, 44.25, 34.5).Select
ActiveSheet.Shapes.AddShape(msoShapeOval, 267#, 102#, 28.5, 30#).Select
ActiveSheet.Shapes.AddShape(msoShapeOval, 246.75, 141#, 30.75, 27.75).Select
End Function
Sub DELETEALLSHAPES()
Set RNG = Range("E8:G14")
For Each SH In ActiveSheet.Shapes
If Not Application.Intersect(RNG, SH.TopLeftCell) Is Nothing Then
SH.Delete
End If
Next
End Sub
What I am trying to do here is select a value from a drop down which is linked to a formula which triggers the macro shape..i have all that down already... but the problem is that I will have multiple values in the same drop down and I needed to figure a way to delete the previous macro shape in that range. And so i have created the delete all shapes code below but it seems whenever I use the code it deletes the data validation drop downs
here is what I have so far...
Function Macro()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _
Select
End Function
Function CIRCLES()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeOval, 203.25, 101.25, 44.25, 34.5).Select
ActiveSheet.Shapes.AddShape(msoShapeOval, 267#, 102#, 28.5, 30#).Select
ActiveSheet.Shapes.AddShape(msoShapeOval, 246.75, 141#, 30.75, 27.75).Select
End Function
Sub DELETEALLSHAPES()
Set RNG = Range("E8:G14")
For Each SH In ActiveSheet.Shapes
If Not Application.Intersect(RNG, SH.TopLeftCell) Is Nothing Then
SH.Delete
End If
Next
End Sub