Forum Discussion
Excel VBA Shape Rotate makes unwanted Move and Resize to Shape Group.
A shape group is getting resized after VBA rotation.
The attached Excel 2016 / Office 365 workbook contains a worksheet with 2 sets of shapes:
- Triangle 1 and Circle 1 (Ungrouped)
- Triangle 2 and Circle 2 (Grouped)
Changing the value (e.g. 1 thru 360) in cell H4 executes Excel's Worksheet_Change script.
This script rotates the triangles inside the circles.
- Triangle 1 rotates properly
- Triangel 2 also rotates, however, it is contained inside of a shape group, and the entire shape group unexpectedly resizes and moves.
Can someone explain why the shape group resizes and moves?
Can someone provide a solution that prevents the shape group from resizing?
I have attached a sandbox xlsm file so you can duplicate this issue for yourself.
Before Triangle RotationAfter Triangle Rotation
11 Replies
My workaround plan is to use Excel VBA to prevent the unwanted move and resize of the shape group is to:
1. Use VBA to ungroup the group object (that way I can rotate my shape without affecting the size or position of the shape group (because my shape will no longer be inside of the group)
For the example on how to do this see:
https://docs.microsoft.com/en-us/office/vba/api/excel.shape.ungroup2. Perform the rotation
3. Regroup the ungrouped shaped objects back into their original group.
For information on regrouping see:
https://docs.microsoft.com/en-us/office/vba/api/excel.shaperange.regroupAny pointers on how to code this in VBA would be appreciated:
1. First save the group name and all of its member shape (and subgroup) names
2. Ungroup the grouped Object
3. Rotate one shape in the group by changing the .Rotation property value
4. Regroup the object back into it's original group name
Grosner Perhaps this gives you some ideas:
Sub Demo() Dim oObj As Object Dim i As Integer Dim oGroup As Object Dim stMembers() As String Dim sGroupName As String 'Set your grouped object to oObj first. 'Then: sGroupName = oObj.Name Set oGroup = oObj.Ungroup If Not oGroup Is Nothing Then ReDim stMembers(1 To oGroup.Count) For i = 1 To oGroup.Count stMembers(i) = oGroup(i).Name Next ' Now rotate any member of the group and then:
' regroup ActiveSheet.DrawingObjects(1, stMembers()).Group.Name = sGroupName End If End SubWith a few tweaks (see attached workbook) Jan's solution enables Excel VBA to ungroup and regroup shapes.
For an alternative that uses fewer lines of code, there is a way to use use the Shapes Ungroup and Shapes Regroup method (ineffective for shape groups that are contained inside another shape group):
'Group 3 Rotate - Solution B
Dim ShGroup As Shape
Sheet1.Shapes("Group 3").Ungroup
Sheet1.Shapes.range(Array("Triangle 3")).Rotation = range("H4").Value
Set ShGroup = Sheet1.Shapes.range(Array("Triangle 3")).Regroup
ShGroup.Name = "Group 3"I have posted both solutions in the attached workbook. Here are the illustrations of the worksheet when the zoom is and is not set to 100%Excel VBA - Rotate while Zoom = 100%Excel VBA - Rotate while Zoom = 98%
Excel Resizes & Moves Shape Objects when Zoom is Not 100%:
As the attached worksheet and images above illustrate, when an Excel Sheet's zoom is not 100%, Excel VBA that changes a shape contained in a grouped shape object will cause the grouped shape and its components to resize and to move if the Excel zoom is not 100%.
Essentially, the values for shape object's the .Left, .Top, .Height, and .Width change when a property, such as .Rotation, is changed. Why this happens is a mystery to me, but a Google search will find other instances of similar behaviors.
Excel VBA Future Support
One would have hoped that Microsoft fixed these issues, however, a Help-> About done while in Excel VBA shows version Visual Basic for Applications 7.1. According to this https://en.wikipedia.org/wiki/Visual_Basic_for_Applications the Excel VBA program first appeared in 1993 and was last updated in Office 2013 (about 6 - 7 years ago as of the date of my writing) when it was updated to version 7.1 so perhaps there is no longer any interest in improving Excel VBA. Perhaps the https://docs.microsoft.com/en-us/visualstudio/vsto/create-vsto-add-ins-for-office-by-using-visual-studio?view=vs-2019 will provide a better alternative for Excel VBA.
A Few More Excel VBA Tips for Working with Shapes
A few lines of Excel VBA Code that came in handy were:
Application.ScreenUpdating = False 'Disable Screen Updating to hide intermediate changes
ActiveWindow.zoom = 100 ' Use to reset zoom to 100% so shapes will not move around
Hi,
I've tried to rotate them, but they both rotate correctly as the screenshot below!
I think there is a bug in your current version of Office, so please make sure you have the latest version of Office 365.
Regards
I am fully updated.
If you look closely at the result image, you will see the image on the right is slightly larger. I am guessing you only entered a value into cell H4 one time. If you enter any value several more times, pressing enter after each entry, I think you will see the changes more clearly.
Also, right click and select size and properties. This should show the same value for height and width for the grouped object. If it does not, then you have replicated my situation.
Ok, I confirm the issue!
It seems like the grouping causes this weird bug!
You can send feedback to the Excel Team about it.
For now, the workaround is to forget the grouping!
Regards
- I'm unable to reproduce the issue, both shape combinations rotate the same and keep their sizes.
Ok, Thanks for trying. It is interesting you cannot duplicate the resize issue.
Are you using Excel 2016 / Office 365 with latest updates applied, as I am?Additionally, be sure to enter a different value into Cell H4 and press enter.
You may need to do this several times in order to see the effect, because the resize and move occur in very small increments.
