Excel: Stop Shape Creation If Already Exists 1

[TopJack]

I have some VBA code in Excel that creates shapes with text inside.

Sub draw_text(x As Integer, y As Integer, text_string As String)
Dim sht1 as Object

Set sht1 = Sheets(1)

With sht1.Shapes.AddShape(msoShapeRoundedRectangle, x, y, 60, 15)
    .TextFrame.Characters.Text = text_string
End With

End Sub

This works fine for me. But here is the twist .......

Would it be possible to check if an exact same shape (and text) already exists in the same location and prevent another creation of the same object. In other words prevent duplication.

I thought about running a FOR-LOOP around all the shapes on the sheet and check individually for an exact match and then prevent the code above running again. The problem I have with this is that I may end up with multiple shapes on one sheet and it would get slower and slower in checking all available shapes.

Any ideas or clues ?

Thanks.

 

[SkipVought]

Hi,

    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
      With shp
        Select Case .Type
            Case msoTextBox
            
            Case msoAutoShape
                Select Case shp.AutoShapeType
                    Case msoShapeRectangle
                    
                    Case msoShapeOval
                    
                End Select
        End Select
      End With
    Next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TonyJollans]

Hi TopJack,

My guess is that the only way there will ever be an exact same shape is if you have created it in the same piece of code. Could you not have some sort of naming convention which would enable you to check for the existence of the appropriately named rectangle?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TopJack]

Thanks Skip and Tony that has given me some good ideas.

I think short term the For-Loop would identify any offending shapes. Longer term (just for speed) by using uniquely identified shapes I could quickly assess whether the shape already exists. Nice one.

When I have done the code I will post back here for reference.

Thanks again guys.

 

[TopJack]

I found out that by uniquely identifying shapes as they are created would mean I could reference them directly (Thanks Tony).

When I referenced them directly and there was a problem it would error, ie if the shape didn't exist already. Therefore if the shape didn't already exist (which was good) meant the code would error. If the shape already existed (which was bad) the code would not error. I thought this would be bad programming practice because intentionally causing good code to error would be a confusing way to resolve the problem.

The only tidy way to check for the shape already being there was to use Skips For-Loop and check for the unique shape name - this way would prevent any error happening.

Thanks again guys.