Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: shapes in certain range

    hi,

    im looking for some vba code that will help me to detemin wich shape is in a certain range
    example

    on sheet1, ik have a dropdown nicely placed in cell B2

    Now i would like to know de name (indexnr) for an object in cell B2

    The only way i can figure this one out, is to loop through all shapes on the sheet and then check on bottemrightcell of topleftcell.

    Is there a way determin de name of the shape in B2???

    tnx,
    Jannick

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    On a similar project (with 73 shapes), I decided to name them with a unique name/number combination, i.e. Mark1, Mark2, etc. You could use the cell reference plus some other name to identify (i.e. Markb1) - you can't use B1 because that is reserved for the cell itself.

    Here is the code I use for them, with arrows indicating the direction and color (up/green, flat/orange, down/red)

    Code:
    Sub OriginalValueCheck()
    'This checks the cells in Column A on worksheet Work (rows 10 through 77)
    Application.ScreenUpdating = False
    'This first command unhides rows 9, 16, 24
        UnhideRows
        Dim i As Long
        Sheets("Work").Select
        For i = 2 To 73
            Select Case Cells(i, 1).Value
            Case Is < 0: RedAutoShape i
            Case Is > 0: GreenAutoShape i
            Case 0: OrangeAutoShape i
            End Select
        Next i
    'This calls the macro for the reverse direction/color of rows 23 and 24
        OriginalValueCheckS
        Sheets("Highlights").Select
    'This macro re-hides rows 9, 16, 24
        HideRows
    Application.ScreenUpdating = True
    
    End Sub
    
    Sub RedAutoShape(i)
        Sheets("Highlights").Select
        With ActiveSheet.Shapes("MattAS " & i)
            .AutoShapeType = msoShapeDownArrow
            .Fill.Visible = msoTrue
            .Fill.ForeColor.SchemeColor = 10
            .Line.ForeColor.SchemeColor = 10
            .Rotation = 0#
        End With
        Sheets("Work").Select
    
    End Sub
    Sub GreenAutoShape(i)
        Sheets("Highlights").Select
        With ActiveSheet.Shapes("MattAS " & i)
            .AutoShapeType = msoShapeUpArrow
            .Fill.Visible = msoTrue
            .Fill.ForeColor.SchemeColor = 57
            .Line.ForeColor.SchemeColor = 57
            .Rotation = 0#
        End With
        Sheets("Work").Select
    
    End Sub
     
    Sub OrangeAutoShape(i)
        Sheets("Highlights").Select
        With ActiveSheet.Shapes("MattAS " & i)
            .AutoShapeType = msoShapeLeftRightArrow
            .Fill.Visible = True
            .Fill.ForeColor.SchemeColor = 52
            .Line.ForeColor.SchemeColor = 52
            .Rotation = 0#
        End With
        Sheets("Work").Select
    
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    ok this is indeed an option
    but
    what i don't understand is how does excel do it?
    I mean if i insert a line above a dropdown, the dropdowns will automaticly be moved 1 line.
    If there's anybody who knows how this works???

    tnx,
    Jannick

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    ik have a dropdown nicely placed in cell B2
    So, is this "dropdown" an object created with the Forms Toolbar or Control Box Toolbar?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    I'm sorry for my late reply but i was ill whole week long

    In reply:
    It's from the form-toolbar

    tnx,
    Jannick

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    You can access the options of a form controls by the following code :


    worksheets("Sheet1").shapes("Line 1").oleformat.object....

    if you want to see all the options of "object" you will have to open the
    Local Varaibles Window in the visual basic editor and step on you code with F8.

    Hope this helps

Posting Permissions

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