Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Test/Check if Shape Exists on Worksheet

  1. #1
    Join Date
    31st August 2006
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th August 2005
    Posts
    844

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st August 2006
    Posts
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    10th August 2005
    Posts
    844

    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:
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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
    VB:
    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 
    
    

  6. #6
    Join Date
    31st August 2006
    Posts
    3

    Re: Test/Check if Shape Exists on Worksheet

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    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 at 18:51.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: July 3rd, 2007, 22:16
  2. Check if Named Chart Exists on Worksheet
    By abaranyay in forum EXCEL HELP
    Replies: 6
    Last Post: August 30th, 2006, 13:37
  3. Replies: 8
    Last Post: May 5th, 2006, 19:18
  4. Check if Worksheet Exists
    By hu66666 in forum EXCEL HELP
    Replies: 6
    Last Post: August 15th, 2005, 00:58
  5. Check if Worksheet Exists
    By Ying&Yang4u in forum EXCEL HELP
    Replies: 13
    Last Post: October 26th, 2004, 22:05

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno