locked
Button shapes destroyed when attempting to reset height and/or width properties. RRS feed

  • Question

  • I have code (below) that attempts to 're-establish' a worksheet 'button' shape's height and width properties for consistency purposes, but it destroys the button shape every time I try it. These button shapes are located on the header (summary) row of each outline group on a worksheet. These shapes are not grouped. I'm not a VBA pro so I'm thinking I must be missing something about how to change these shape properties.

    Strangely, I can change the height and width properties of a worksheet textbox shape without any problem. What might I be doing wrong here?

    Using Excel 2013, 32bit on Windows 10 64bit.

    290           With shp
    
                      'Unlock the grp shape
    300               .Locked = False
    
                      'Format the calling shape to maintain consistency
                      '.Height = 0.2
                      '.Width = 0.26
    310               With .TextFrame
    320                   .MarginLeft = 0.02
    330                   .MarginTop = 0.03
    340                   .HorizontalAlignment = xlHAlignCenter
    350                   .VerticalAlignment = xlVAlignCenter
    360                   With .Characters
    370                       With .Font
    380                           .Name = "Wingdings 3"
    390                           .Size = 12
    400                       End With
    410                       .Text = Chr(69)
    420                   End With
    430               End With
    
                      'Toggle the expanded/contracted state of the group
    440               With Range("TemplatesbySource").Parent.Rows(GrpRow)
    450                   If .ShowDetail = True Then
    460                       .ShowDetail = False
    470                   Else
    480                       .ShowDetail = True
    490                   End If
    500               End With
    
                      'Relock the group shape
    510               .Locked = True
    
    520           End With




    phillfri



    • Edited by phillfri Tuesday, December 22, 2015 5:04 AM
    Tuesday, December 22, 2015 4:56 AM

Answers

  • Hi phillfri,

    >>I have code (below) that attempts to 're-establish' a worksheet 'button' shape's height and width properties for consistency purposes, but it destroys the button shape every time I try it. These button shapes are located on the header (summary) row of each outline group on a worksheet.<<

    What did you mean that the shape was destroyed? Did you get any error message?

    The code works well for me change the shape's width and height if I uncomment the code below:

    .Height = 0.2
    .Width = 0.26
    

    You can get more detail about Shape object from link below:
    Shape Object (Excel)

    If you still have the problem, please let us know more detail about the issue.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 23, 2015 5:39 AM