Results 1 to 2 of 2

Thread: Align Shapes (Charts) On ActiveWorksheet

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14

    Lightbulb Align Shapes (Charts) On ActiveWorksheet

    Many Time we have Lot of Shapes On a Worksheets and we have to Align All those Shapes . This is a Code snippet to Make this Task easy.


    Code:
    Sub MakeMyShapes()
    
    Dim intICounter         As Integer
    Dim shpShape            As Shape
    Dim sngHeight           As Single
    Dim sngWidth            As Single
    Dim IntNShapes          As Integer
    Dim intleft             As Integer
    Dim intTop              As Integer
    Dim bytRow              As Byte
    Dim intMaxH             As Integer
    Dim intMaxW             As Integer
    Dim intTempArr()        As Integer
    
    bytRow = Application.InputBox("Please enter Number of Rows (0-255)")
        With ActiveSheet
            IntNShapes = .Shapes.Count
            intleft = .Shapes(1).Left
            intTop = .Shapes(1).Top
            sngHeight = .Shapes(1).Height
            sngWidth = .Shapes(1).Width
            
            ReDim intTempArr(IntNShapes)
                For intICounter = 0 To IntNShapes - 1
                    intTempArr(intICounter) = .Shapes(intICounter + 1).Height
                Next
                intMaxH = WorksheetFunction.Max(intTempArr)
            
                For intICounter = 0 To IntNShapes - 1
                    intTempArr(intICounter) = .Shapes(intICounter).Width
                Next
                intMaxW = WorksheetFunction.Max(intTempArr)
            
            For intICounter = 1 To IntNShapes
                .Shapes(intICounter).Left = intleft
                .Shapes(intICounter).Top = intTop
                If intICounter Mod bytRow = 0 Then
                    intleft = .Shapes(intICounter + 1 - bytRow).Left + sngWidth
                    intTop = .Shapes(1).Top
                Else
                    intleft = .Shapes(intICounter).Left
                    intTop = .Shapes(intICounter).Top + sngHeight
                End If
                    .Shapes(intICounter).TextFrame.Characters.Text = intICounter
                Next
        End With
    End Sub

  2. #2
    Junior Member
    Join Date
    May 2012
    Posts
    25
    Rep Power
    0
    Hi,

    here intMaxH & intMaxW are never used.

    Code:
                If intICounter Mod bytRow = 0 Then
                    intleft = .Shapes(intICounter + 1 - bytRow).Left + sngWidth
    in above code, if bytRow=255, then wouldnt the shape be out of visible screen?


    and in this 2nd loop it gives an error:
    Code:
                For intICounter = 0 To IntNShapes - 1
                    intTempArr(intICounter) = .Shapes(intICounter).Width
                Next
                intMaxW = WorksheetFunction.Max(intTempArr)
    Last edited by Junoon; 05-14-2012 at 03:57 AM.

Similar Threads

  1. Did You Know? :: Formatting Shapes in Powerpoint & Excel: Setting Defaults
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-19-2013, 08:35 AM
  2. Adding charts via code to a protected sheet
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 11-14-2012, 05:11 PM
  3. Index Sheets with Shapes and Hyperlink
    By TomyLee in forum Excel Help
    Replies: 9
    Last Post: 09-04-2012, 10:52 PM
  4. Replies: 2
    Last Post: 02-29-2012, 08:24 PM
  5. VBA - Secondary axis - CHARTS
    By albi85 in forum Excel Help
    Replies: 1
    Last Post: 04-05-2011, 03:56 AM

Tags for this Thread

Posting Permissions

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