Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

Solved

Can I use VBA to apply corner radius on text box?

Posted on 2013-01-06

1 solution

Medium Priority

1,186 Views

Last Modified: 2013-01-17

Hi All

I have a textbox with a table in it (not sure if that it a terrible idea, but it seems to be working) and I would like to apply rounded corners to the text box such that a specific radius is maintained...

I tried to record a macro to see what happens... but this is all I got:

ActiveDocument.Shapes.Range(Array("Rounded Rectangle 5")).Select

(changing the corner was not recorded, grr)

help on this much appreciated.

Oh BTW - if this is a terrible idea (I'm using cross references and headings in my table & textbox) please let me know...

Cheers, S

Comment

Question by:DrTribos

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 53

Expert Comment

by:Rgonzo1971

ID: 387482272013-01-06

Hi,

Please refer to http://excelusergroup.org/forums/t/2450.aspx

Public Sub AddRoundRect()
    Dim sht As Worksheet
    Dim rect As Shape
  
    Set sht = ActiveSheet
    Set rect = sht.Shapes.AddShape(msoShapeRoundedRectangle, 400, 110, 150, 75)
    With rect.TextFrame2
   
        .TextRange = "Some Text In Here"
        .VerticalAnchor = msoAnchorMiddle
        .TextRange.ParagraphFormat.Alignment = msoAlignCenter
    End With
End Sub

Open in new window

Regards

LVL 76

Expert Comment

by:GrahamSkan

ID: 387483752013-01-06

Hi Steve,
This seems to work for me:

    Dim tb As Shape
    
    Set tb = ActiveDocument.Shapes(1)
   tb.AutoShapeType = msoShapeRoundedRectangle

Open in new window

LVL 15

Author Comment

by:DrTribos

ID: 387484202013-01-06

Hi Rgonzo1971 & Graham

Thanks for the code. As far as I can tell the parameters that can be passed to msoShapeRoundedRectangle are to position the shape on the page and do not specify the radius...

The problem for me is that the text box resizes as text is entered / removed and the radii on the corners changes... I'd rather my (my users) radii to remain constant... (I have a similar problem with callouts... the tail changes size with the textbox... most annoying).

I tested msoShapeRoundedRectangle after dragging the shape around a bit and it does indeed put the radii back to some default... but this depends on height & width :-(

Cheers, S

LVL 76

Expert Comment

by:GrahamSkan

ID: 387484532013-01-06

I don't think that there is any way to directly control the radii of the corners. They depend on the size of the shape, so that they stay in proportion.

LVL 53

Expert Comment

by:Rgonzo1971

ID: 387700282013-01-12

Hi,

You could refer to

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_11096-How-to-set-and-determine-the-rounding-in-AutoShapes-with-VBA.html

Regards

LVL 15

Author Comment

by:DrTribos

ID: 387733222013-01-13

Hi Rgonzo - I get an EE Unauthorized Access Error when I try to follow your link. It says the question has not yet been closed...

Thanks, S

LVL 53

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 1600 total points

ID: 387734712013-01-13

Sorry

Not yet reviewed

Sub GetRadius()

    Dim oSh As Shape
    Dim sngRadius As Single ' Radius size in points
    
    Set oSh = ActiveWindow.Selection.ShapeRange(1)

    With oSh
        If .Width < .Height Then
            sngRadius = .Width * .Adjustments(1)
        Else 'oSh.Width >= oSh.Height
            sngRadius = .Height * .Adjustments(1)
        End If
    End With
    
    MsgBox sngRadius

    Set oSh = Nothing

End Sub
 

 
Sub SetRadius()

    Dim oSh As Shape
    Dim sngRadius As Single ' Radius size in points

    sngRadius = 40

    Set oSh = ActiveWindow.Selection.ShapeRange(1)

    With oSh
        If .Width < .Height Then
            .Adjustments(1) = sngRadius / .Width
        Else 'oSh.Width >= oSh.Height
            .Adjustments(1) = sngRadius / .Height
        End If
    End With

    Set oSh = Nothing

End Sub

Open in new window

Regards

LVL 76

Expert Comment

by:GrahamSkan

ID: 387736812013-01-14

Rgonzo1971,

I, for one, can thank you.

Previously, I couldn't find how to adjust the radius of the corners manually, so assumed that no such adjustment was possible. Your code prompted me to persevere.

The secret is to drag the yellow diamond shape near the top left of the selected shape. It is possible to do this while macro recording is on, so that the index in the Adjustments collection can be confirmed from the recorded code.

I hope that this information is in your article.

LVL 15

Author Closing Comment

by:DrTribos

ID: 387894782013-01-17

Hi Rgonzo - thank you!

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Delivering a Word Template for Use in Merging Data from Access

Article by: Helen

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…

New Microsoft Office 365 Sign-in experience

Article by: Shaun

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.

Building Probability Models in Excel Part 4: Discrete Random Variables

Video by: Toby

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

How to Create a Table of Contents in Word

Video by: Leslie

Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …