Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi,

    I have a workbook, where I can attach shapes through VBA-code. How do Ihave to adjust my code to attach the shapes of the org-chart in it???
    I wonder, how this can be possible. ... I can only see the different shapenames when I toggle through the layout menu...
    does someone know how to solve this question???

    stefan
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The "problem" is that orgchart shapes are grouped. Try this. It lists all the shape names and then goes through the grouped items. It lists all the items in the group (which includes the lines) and the text for the rounded rectangles

    Steve

    [codebox]Option Explicit
    Sub GetShapeNamesText()
    Dim shp As Shape
    Dim i As Long
    Dim x As Integer
    i = 1
    For Each shp In ActiveSheet.Shapes
    ActiveSheet.Range("N2").Offset(i, 0).Value = shp.Name
    i = i + 1
    For x = 1 To shp.GroupItems.Count
    ActiveSheet.Range("N2").Offset(i, 0).Value = _
    shp.GroupItems(x).Name
    If shp.GroupItems(x).AutoShapeType = msoShapeRoundedRectangle Then
    ActiveSheet.Range("N2").Offset(i, 1).Value = _
    shp.GroupItems(x).TextFrame.Characters.Text
    Else
    ActiveSheet.Range("N2").Offset(i, 1).ClearContents
    End If
    i = i + 1
    Next x
    Next shp
    End Sub[/codebox]

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve, this is
    cool !!!

    so i have to adjust my other code with the group statement to get the information out or in an org chart... eh?
    i will give it a try.

    so I should say,
    live long and prosper,
    if I remember some famous words of my childhood correct

  4. #4
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well, I tried to do so and I only got out the names. I tried to set the oder routines with the adjustments but I think, the routines have to be cloned and renamed for the case I have some org charts instead normal shapes? Can You tell me, how i can cope with the cases Grouped/ungrouped shapes - ie. Org charts and normal shapes to get some text in and out like the names out???
    Stefan

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The routine I did shows how to get the text and Name OUT. It is something like (this gets the name / text for the 10th grouped item in shape 1 on the active sheet):

    sName = ActiveSheet.Shapes(1).GroupItems(10).name
    sText = ActiveSheet.Shapes(1).GroupItems(10).textframe.cha racters.text

    The problems are that not all items have text and give an error (that must be trapped/ignored) if there is no text. Also some shapes are not grouped and trying to access the grouped items from ungrouped shapes causes an error.

    You can even rename the shape using:
    ActiveSheet.Shapes(1).GroupItems(10).name = "NewName"

    Now, I would have thought that one could add new Text using
    ActiveSheet.Shapes(1).GroupItems(10).textframe.cha racters.text = "New Text"

    But this gives an error, even though this technique works for ungrouped shapes:
    ActiveSheet.Shapes(2).textframe.characters.text = "New Text"

    Org Charts seem to be a little "different" than other shapes as they are grouped but ungrouping them is inactive.

    You are past what I have been able to find with playing, and you may have to wait for someone with more expertise in working with shapes and working with org charts shapes in particular. According to this thread http://www.eggheadcafe.com/software/...ctanglete.aspx from MS MVP Jon Peltier, he has not found a way to add text using code to a grouped item. If org charts are different (they may have their own object model and code that I am unaware of) you will have to find that as well.

    I am sorry I could not help more, but I have come to a wall...

    Good Luck,
    Steve

  6. #6
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, You tried Your best ;-)

Posting Permissions

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