Announcement

Collapse
No announcement yet.

Test/Check if Shape Exists on Worksheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Test/Check if Shape Exists on Worksheet

    Hi,

    I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook)
    When the users insert a new picture he has to set a name for each of them.
    A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.

    But I have a bug if a shape doesn't exist when I try to select it :

    Sheets("fiche de controle").Select
    'select the sheet with the pictures
    ActiveSheet.Shapes(Item_old).Select
    'select the shapes "Item_Old"
    Item_Old is a variable corresponding to the picture name.

    If the user made a typo, I have a bug.
    Someone knows how to test if the shapes exist to display a meesage if not ?
    Or somethig to avoid this kind of bug ?

    P.S: Moreover to learn about VB I train my english too, I'm a swiss guy

    Many Thx

  • #2
    Re: Test if a shape exist

    Welcome Stef!

    A couple of options: Since you are already using some sort of userform for the input, you can fill the list of available shapes for the user to select from a pulldown rather than have them type it in.

    In the short term, you should be able to do a simple "if ActiveSheet.Shapes(Item_Old) then" to detect if it exists.
    ---
    Old Programmers Never Die ... They just lose their bits

    Comment


    • #3
      Re: Test if a shape exist

      Hi ShosMeister,
      Thx for your replay.

      I don't use a userform for my inputs, then your second suggestion is nice.
      But I dont understand all :

      "if ActiveSheet.Shapes(Item_Old) then"

      Something is missing, No?
      How can I code the test of the "if" procedure??

      "if ActiveSheet.Shapes(Item_Old).exist then"

      Stef

      Comment


      • #4
        Re: Test if a shape exist

        Ahh. Sorry. I left it blank as you can use any property/attribute of the shape to test if it exists. Try this:
        Code:
        Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
          On Error Resume Next
          tmpID = ActiveSheet.Shapes(Item_Old).ID
          If IsEmpty(tmpID) Then
            Debug.Print "Not there"
          Else
            Debug.Print "Looks like it's there"
          End If
          On Error GoTo 0
        End Sub
        ---
        Old Programmers Never Die ... They just lose their bits

        Comment


        • #5
          Re: Test if a shape exist

          Stef32, please use Code Tags as you agreed to do when joining and when posting a New Thread.

          I think you should READ the rules you have agreed to.

          BTW, try this method
          Code:
          Sub CheckForShape()
          Dim sHape As sHape
          
              On Error Resume Next
              Set sHape = Sheet1.Shapes("Box 1")
              On Error GoTo 0
          
              If sHape Is Nothing Then
                 MsgBox "Box 1 does not exist on " & Sheet1.Name
                 Exit Sub
              End If
            
              'YOUR CODE
          End Sub

          Comment


          • #6
            Re: Test/Check if Shape Exists on Worksheet

            Hi,
            Thkx very much, it work well.
            As we say....MERCI BEAUCOUP...!!!
            Stefano

            Comment


            • #7
              Re: Test/Check if Shape Exists on Worksheet

              Stef32
              Junior Member
              PLEASE DO NOT REPLY TO THIS POST UNTIL THE QUESTION ASKERS HAS AKNOWLEDGED THE TEXT BELOW
              Poted by Dave Hawley:
              Stef32, please use Code Tags as you agreed to do when joining and when posting a New Thread.

              I think you should READ the rules you have agreed to.

              BTW, try this method
              ---
              jiuk
              Last edited by Jack in the UK; August 31st, 2006, 18:51.

              Comment

              Working...
              X