PDA

View Full Version : [SOLVED] Delete Scooby (Shape)



Juriemagic
07-30-2015, 07:52 AM
Hi good people!,

I have put together a code but only the first half works. According to the code when A2 < 3 and B2 = 0, I want a named shape plotted, and value in B2 must be made 1. If the value in A2 > 2 and B2 = 1, I want the shape deleted, as well as the value in B2 must change back to 0. This is the part I cannot get to work.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A2") < 3 Then
If Range("B2") = 0 Then
Range("B2").FormulaR1C1 = 1
application.ScreenUpdating = False
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 100, 150, 100, 100). _
Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.150000006
.Transparency = 0
.Solid
End With
Selection.ShapeRange.Line.Visible = msoFalse
Selection.ShapeRange.Name = "Scooby"
Selection.Name = "Scooby"
Range("B3").Select
Range("E8").Select
End If
If Range("A2") > 2 And Range("B2") = 1 Then
Range("B2").FormulaR1C1 = 0
ActiveSheet.Shapes.Range(Array("Scooby")).Select
Selection.Delete
Range("E8").Select
End If
End If
End Sub
The code is:

Please assist me with this minor adjustment...I have tried all I could, just no joy. Thanx a lot people!

jonh
07-30-2015, 08:32 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case True
Case Target.Address <> "$A$2" And Target.Address <> "$B$2"
Case Range("A2") < 3 And Range("B2") = 0
With ActiveSheet.Shapes.AddShape(msoShapeRectangle, 100, 150, 100, 100)
.Name = "Scooby"
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = -0.150000006
.Fill.Transparency = 0
.Fill.Solid
.Line.Visible = msoFalse
End With
Case Range("A2") > 2 And Range("B2") = 1
Range("B2") = 0
ActiveSheet.Shapes.Range(Array("Scooby")).Delete
End Select
End Sub

Juriemagic
07-30-2015, 11:16 PM
Thanx a lot jonh..you have been a great help!!. Your time is really appreciated!

Juriemagic
07-30-2015, 11:43 PM
jonh,

Please forgive me, I do have a slight problem. The first thing is that I failed to give you the precise scenario:. It is that A2 changes as result of a drop down. I did not realize that this would actually make such a big difference, I am sorry. So, I select my drop down, A2 changes, but no execution takes place. Only once I click anywhere on the sheet, does the code kick in...Once again, please accept my apology for this, this was also a learning curve for me..

Juriemagic
07-31-2015, 02:05 AM
Hi jonh,

Ignore post #4..It's sorted. Thank you very much..