Forum Discussion
Excel VBA Shape Rotate makes unwanted Move and Resize to Shape Group.
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.ungroup
2. 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.regroup
Any 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 Sub
With 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 aroundGrosner VBA future: Don't expect them to do any updates to the VBA editor. They do state that new functionality will be added to the object model. Omissions and bugs in the OM are likely to be the same for VBA and for the Visual Studio Office COM interface. If you want to be future-proof, start learning JavaScript and the new JS Office programming paradigm. That too has limitations though.