Selecting drawing objects or shapes in a macro

[John DeFiore]

I have an application where an Excel cell contains either
a red dot or a red dot with a circle around it. These are
drawing objects or shapes. When I delete the contents of
a cell, they stay put. I have to select them individually
and delete them, which is what I want to automate with a
bit of code. What I want to do is delete the dot within
the active cell, and delete the contents of the cell. I
just can't figure out how to select the dot in the active
cell without knowing the individual shape range. What I
want to do is:

ActiveSheet.Shapes("Oval 955").Select

But I want to select whichever "Oval" is in the active
cell. Is that possible?

Thanks,

John

 

[shockley]

This will delete the shape in the active cell (assuming the shape occupies
only one cell) and clear the contents of the active cell, which is what I
think you want to do (I don't guarantee this is the easiest way):

Sub Tester2()
With ActiveSheet
For i = 1 To .Shapes.Count
x = .Shapes(i).TopLeftCell.Row
y = .Shapes(i).TopLeftCell.Column
If ActiveCell.Address = .Cells(x, y).Address Then
.Shapes(i).Delete
ActiveCell.ClearContents
Exit For
End If
Next i
End With
End Sub

HTH,
Shockley

 

[John DeFiore]

Did the trick exactly as I wanted, thanks much.

John

 

[DatBAYG]

I created a template where users may want to show two black boxes on the
page at any point. The sheet has to be printed two ways. Once plain
and once with these two black boxes which cover up the values in two
columns. I created buttons to turn these boxes "on" and "off." I
don't delete the boxes when the user wants to change them, I just have
a macro attached the buttons which change their color to white (or
transparent).

If you happen to create buttons to toggle your circles, you can right
click on the button, choose Format Control, and uncheck Print object
under the Properties tab. This way, the user can see the buttons at
all times, but they won't print.

I see you already know the name of your object. The only tricky part
about this is that.. if you protect the sheet, you have to make sure
that either object your are selecting to change is in an unlocked cell
OR you choose to allow the user to "Edit Objects." If you allow them
to edit objects, they could change the buttons on your form, but you
can always send them to another cell so that they don't even notice
they have this privilege. You need to go to your VB editor and create
a module under your project, then add this... to some extent. Then you
would have to go back to your sheet and attach the macros to your
buttons (if you're going to use buttons).

Your code would look like:


Code:
--------------------

Sub DotOff()

ActiveSheet.Shapes("Oval 26").Select
Selection.ShapeRange.Fill.Visible = msoFalse
Range("A10").Select

End Sub

Sub DotOn()

ActiveSheet.Shapes("Oval 26").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Range("A10").Select

End Sub

--------------------



If you want to do something tricky like send the user back to whatever
range they were in... you'll have to assign their range to some
variable, then send them there afterwards. I'll let you figure that
one out. Its too early in the morning for me!